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.

 

[L. Howard]

Hi hitim1

Does this do it?

Howard

Option Explicit

Sub myCalculate()

 Range("D1") = WorksheetFunction.Sum(Range("A:C"))

 End Sub

 

[Michael M]

Or, if you want to loop through all rows

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

 

[htim1]

Thanks but no it doesn't give me what I want. I want to go through each row, and put the sum for each row. Not just D1. For example, if A2 + B2 + C2 = D2, A3 + B3 + C3 = D3 etc and loop it so I can get a calculation for each row where there is a figure in rows A2 onwards. A1 to D1 are headings so I don't want them included in my calculations.

 

[htim1]

Or, if you want to loop through all rows

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

Thanks but this doesn't work - I get zero in D1 when I add your code to:

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" & i) = WorksheetFunction.Sum(Range("A" & i & ":C" & i))
Next i

End Sub


Any other suggestions?

 

[Michael M]

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

 

[L. Howard]

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

Typo? Row 2 on down.

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

Howard

 

[Michael M]

@Howard
Thanks for the pickup....it's beer o clock....and I have my priorities...

 

[sai85]

@Howard
Thanks for the pickup....it's beer o clock....and I have my priorities...

Sub SUM1 ()
Range("D2").Select
Range("D2").Value = "=Sum("A2:C2"
Activecell.copy
Activecell.offset(0,-1).end(XlDown).offset(0,1).Select
Range(Activecell,Range("D2")).PasteSpecial
End Sub

This will do I Guess...

 

[Michael M]

ummm, pardon me ???

This will do I Guess...

means what exactly ??