Sum a table in VBA

[Cal_Lefty21]

I am trying to use the sum function in VBA to sum columns B, C, and D. the Data starts in B5 and I want the Sums to appear in B4, C4, D4. I am trying to use .end(xlDown) so if data is entered later the sum will pick up the newly entered information without having to change the range. This is what I have so far below but I am unable to get the Sum calculations to appear in B4, C4, D4

Sub calcSums()


Dim EndRow As Integer
Dim EndCol As Integer

Dim cell As Range

With Range("B5")


EndRow = .Offset(1, 0).End(xlDown).Row
EndCol = .Offset(0, 1).End(xlToRight).Column



For Each cell In Range(Cells(EndRow, .Offset(0, 1).Column), Cells(EndRow, EndCol))
cell.FormulaR1C1 = "=SUM(R[-109]C:R[-1]C)"
Next

End With
End Sub


Thanks!

 

[Jorge_B]

Hi,
I don't understand if you want to sum the three columns all together or each column individually.

If you want to sum the three columns at once, try this:

Sub SumCols()
Dim LastRow As Long
Dim Sum     As Long
Dim SumRng  As Range
LastRow = Range("B65636").End(xlUp).Row
Set SumRng = Range("B5:D" & LastRow)
Sum = Application.WorksheetFunction.Sum(SumRng)
Range("B4:D4") = Sum
End Sub

If you want to sum each column individually, try this:

Sub SumEachCol()
Dim i       As Integer
Dim LastRow As Long
Dim Sum     As Long
Dim SumRng  As Range
For i = 2 To 4
LastRow = Cells(65636, i).End(xlUp).Row
Set SumRng = Range(Cells(5, i), Cells(LastRow, i))
Sum = Application.WorksheetFunction.Sum(SumRng)
Cells(4, i) = Sum
Next i
End Sub

Hope it helps.