Edit numbers until sum value has reached

I wish to have a VBA macro that will help me to edit values in the column 'C' for each row until the sum value has reached. However, there are some criteria:

• The value has to be smaller than the value in column 'B'
• If the value in column 'B' is zero, then the value in column 'C' should be zero

For example:

I have certain values in column A for each row and I want the sum for Column C to be 10. Hence, the VBA will loop and iterate each row in column C and check if the number in Column B is greater than 0, if yes then it will add 1 to it. After going through each row, it will check the sum, if the sum has not reach the certain amount (in this case, it's 10), it will loop back again and add 1 to each row and stop when it reaches the sum.

Example output:

----------------------  Column B  | Column C  ----------------------    124     |     3    100     |     3    83      |     2    23      |     1    4       |     1    0       |     0  -----------------------  

Code:

Sub Loop()  Dim Report As Worksheet  Set Report = Excel.ActiveSheet    Dim cell As Range  Dim i As Integer  Dim total As Integer    total = Range("C8").Value  Range("C2:C7").ClearContents    For total = 0 To 10   For Each cell In Range("C2:C12")      For i = 2 To 7          If Range("B" & i).Value > 0 Then              cell.Value = cell.Value + 1              If cell.Value > Range("B" & i).Value Then                  cell.Value = cell.Value              End If          Else:              cell.Value = 0          End If            Next i   Next cell   total = total + Range("C8").Value    Next total    End Sub  

However, the output that I got seems to be not my desire output and I got all zeros instead. I am a newbie to VBA :(, can anyone help me with this?

https://stackoverflow.com/questions/67327205/edit-numbers-until-sum-value-has-reached April 30, 2021 at 09:49AM