VBA - Insert Sum Formula and Loop

Question

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

Tim Williams · Accepted Answer · 2022-05-25 22:33:44Z

2

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

answered May 25, 2022 at 22:33

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA - Insert Sum Formula and Loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related