CODEWITHSUNDEEP
excelvbaloops
Ravi Chugh
Ravi Chugh

Reputation: 1

VBA - Insert Sum Formula and Loop

I have a table which has around 50 columns. Below function works fine. But how can I put a loop across the columns? I cannot write formulas for each column. I cannot use WorksheetFunction.Sum. I require Sum formula to be added in the cell.

Sub Sum_Last_Row()

With Worksheets("Revenue_forecast_table")
    Dim LastRow As Long
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("E" & LastRow + 1).Formula = "=SUM(E5:E" & LastRow & ")"
    .Range("F" & LastRow + 1).Formula = "=SUM(F5:F" & LastRow & ")"
    .Range("G" & LastRow + 1).Formula = "=SUM(G5:G" & LastRow & ")"
    .Range("H" & LastRow + 1).Formula = "=SUM(H5:H" & LastRow & ")"
    .Range("I" & LastRow + 1).Formula = "=SUM(I5:I" & LastRow & ")"
    .Range("J" & LastRow + 1).Formula = "=SUM(J5:J" & LastRow & ")"
End With

End Sub

Upvotes: 0

Views: 175

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

You can populate the whole row at once and the formula will auto-adjust:

Sub Sum_Last_Row()
    Dim NextRow As Long
    With Worksheets("Revenue_forecast_table")
        NextRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
        .Range("E" & NextRow & ":J" & NextRow).Formula = _
                           "=SUM(E5:E" & NextRow - 1 & ")"
    End With
End Sub

Upvotes: 2

Related Questions