VBA Sum Loop formula

[htim1]

hi - I'm new to vba and I am trying to work out how to sum cells in each row via VBA.

Example: I want to sum values in columns A, B, and C for each row where the last row may vary and return the sum value in column D.
I've started with:

Sub Calculate()

Dim lastRow As Long
Dim i As Integer
Dim totalSum as Double

'where totalSum = A + B + C = D

lastRow = Range("A2").End(xlUp).Row

For i = 1 To lastRow
Range("D") = WorksheetFunction.Sum(Range("A" & i & "B" & i & "C"))
Next

End Sub

Could someone please help me fix this - it isn't working and I'm not sure how to fix. I have googled and check this forum but I can't seem to work it out.

 

[sai85]

ummm, pardon me ???


means what exactly ??

Try My code Michael!!!

 

[htim1]

this will do from row 2 to the last row !

Sub Calculate()
Dim i As Long
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Range("D" & i) = WorksheetFunction.Sum(Range("A" & i & ":C" & i))
Next i
End Sub

YES!!! It worked!!! Thanks so much Michael - you are a genius!

Thanks L Howard - I did change the i = 1 to i =2 as you picked up - if I didn't D1 heading would change to zero whenever I activate the code.

Thank you all who have responded - much appreciated.

 

[Michael M]

In that case, why not simply

Sub MM1()
Range("D2:D" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=Sum(A2:C2)"
End Sub

 

[htim1]

Hi Michael - could you please advise how I can just add A and C? I tried to remove the : next to C, assuming that would only add A and C, but it came up with an error.

Sub Calculate()
Dim i As Long
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Range("D" & i) = WorksheetFunction.Sum(Range("A" & i & ":C" & i))
Next i
End Sub

 

[htim1]

I figured it out, I replaced the colon with a , !!! Below adds on A and C



Sub Calculate()
Dim i As Long
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Range("D" & i) = WorksheetFunction.Sum(Range("A" & i & ",C" & i))
Next i
End Sub