How to loop through a column adding values from a second column?

Question

I have two columns in a table, let's say a and b. The value of the cells in a, when the macro is implemented, will be a = a + b. After the addition is performed, all the values in column b will be set to 0.

I've tried using a for loop to loop through every cell in the columns and adding the values but nothing happens.

Sub zeroAndAdd0_Click()

    For i = 2 To NumRows
        Cells(i, 4).Value = WorksheetFunction.Sum(Cells(i, 4).Value, Cells(i,5).Value)
    Next i    

End Sub

No error message occurs but nothing actually happens when I test it.

Right before the `For` loop, set your `NumRows` variable (currently it's not set so your loop isn't looping anything). `NumRows = 10`. Then you should see some magic happening. I just guessed at `10` though so you'll need to set that to whatever makes sense for your sheet. Once that is working then you may want to figure out how to [dynamically set `NumRows` to the last occupied row in one of these columns](https://stackoverflow.com/questions/39470412/last-row-in-column-vba). — JNevill, Jun 12 '19 at 13:28

score 3 · Answer 1 · edited Jun 12 '19 at 13:48

Make sure you always write Option Explicit on the top of the module. Thus, it will make sure that all the variables in the code are declared. In the code above it was not entering the loop, because NumRows was undeclared, thus with a value of 0. Try this instead:

Option Explicit

Sub TestMe()

    Dim i As Long
    For i = 2 To 10
        With Worksheets(1)
            .Cells(i, 4).Value = WorksheetFunction.Sum(.Cells(i, 4).Value, .Cells(i, 5).Value)
        End With
    Next i

End Sub

Error 1004 · Answer 2 · 2019-06-12T13:55:07.920

Try:

Option Explicit

Sub zeroAndAdd0_Click()

    Dim NumRows As Long

    'Change Sheet name
    With ThisWorkbook.Worksheets("Sheet1")

        'NumRows take tha value of a fix number
        NumRows = 10
        'NumRows take the value of Column D last row
        NumRows = .Cells(.Rows.Count, "D").End(xlUp).Row

        .Range(.Cells(2, 4), .Cells(NumRows, 4)).Formula = "=Sum(D2,E2)"

    End With

End Sub

score 0 · Answer 3 · answered Jun 12 '19 at 13:30

I don't think NumRows is populated, if the code is completely as in your question

Try

Sub zeroAndAdd0_Click()
Dim i As Long, NumRows As Long, csum As Double
Dim c As Range

With Workbooks(REF).Sheets(REF)
    NumRows = .Cells(.Rows.Count, "D").End(xlUp).Row 'last row in Col D
    'For each is faster than For i when looping through a range
    For Each c In Range("D2:D" & NumRows) 
        csum = c.Value + c.Offset(,1).Value 'Populate a variable with the sum of the two cells
        c.Value = csum 'Populate the cell in col D with the sum
        c.Offset(,1).Value = 0 'Set the other cell to 0
    Next c
End With

End Sub

Info in the commented out code

score 0 · Answer 4 · answered Jun 12 '19 at 13:32

This will work:

Sub zeroAndAdd0_Click()

    For i = 2 To Cells(Rows.Count, 4).End(xlUp).Row

        Cells(i, 4).Value = Cells(i, 4).Value + Cells(i, 5).Value
        Cells(i, 5).Value = 0

    Next i

End Sub

It will do both of the job for you. no need to use Worksheet Function

score 0 · Answer 5 · edited Jun 12 '19 at 13:32

You just need to define your NumRows variable (i.e. NumRows = Sheet1.Range("D" & Rows.Count).End(xlUp).Row), then finish off with converting the values in column E to 0. So something like:

Sub zeroAndAdd0_Click()

    Dim NumRows, i As Long
    NumRows = Sheet1.Range("D" & Rows.Count).End(xlUp).Row

    For i = 2 To NumRows
        Cells(i, 4).Value = WorksheetFunction.Sum(Cells(i, 4).Value, Cells(i, 5).Value)
        Cells(i, 5).Value = 0
    Next i

End Sub

How to loop through a column adding values from a second column?

5 Answers5