How can I iterate through a row, while using the sum formula for the column above in a for-loop?

Question

I would like to iterate through a row, while summarizing the value of the columns above in a formula. At this point I have data from column 'A' to column 'AV'

The code below does what I want it do, but I'd like to get it into a for-loop that runs preferably to the last column with data. So that I don't have to write 40+ lines of repetitive code.

Dim LastRow As Long
LastRow = ThisWorkbook.Worksheets("Planteleveranse trær 2019").Range("F3").End(xlDown).Row
ThisWorkbook.Worksheets("Planteleveranse trær 2019").Cells(LastRow + 1, "F").Formula = "=SUM(F3:F" & LastRow & ")"

ThisWorkbook.Worksheets("Planteleveranse trær 2019").Cells(LastRow + 1, "L").Formula = "=SUM(L3:L" & LastRow & ")"

ThisWorkbook.Worksheets("Planteleveranse trær 2019").Cells(LastRow + 1, "M").Formula = "=SUM(M3:M" & LastRow & ")"

May I ask why would you want that? Except the `xlDown`, you have what you want? If you do not want the formual to show then in the end simply type this `ThisWorkbook.Worksheets("Planteleveranse trær 2019").Cells(LastRow + 1, "F").Value = ThisWorkbook.Worksheets("Planteleveranse trær 2019").Cells(LastRow + 1, "F").Value` — Siddharth Rout, Aug 05 '19 at 06:03
I think my questions was poorly asked, thanks for pointing that out. The code does what I want it do to, but I wish to extend it. I need to summarize the entire row from 'F' as I have done in my third line of code all the way column 'AV', and I expect more columns of data to be added in the future. I would like this done in a for loop like ` For char c = 'F' to c = 'AV' ThisWorkbook.Worksheets("Planteleveranse trær 2019").Cells(LastRow + 1, "c").Formula = "=SUM(c3:L" & LastRow & ")" ` But I do not know the syntax for that in vba. — Glenn Bergstrøm, Aug 05 '19 at 06:32

score 2 · Accepted Answer · answered Aug 05 '19 at 06:46

I think my questions was poorly asked, thanks for pointing that out. The code does what I want it do to, but I wish to extend it. I need to summarize the entire row from 'F' as I have done in my third line of code all the way column 'AV', and I expect more columns of data to be added in the future. I would like this done in a for loop like For char c = 'F' to c = 'AV' ThisWorkbook.Worksheets("Planteleveranse trær 2019").Cells(LastRow + 1, "c").Formula = "=SUM(c3:L" & LastRow & ")" But I do not know the syntax for that in vba. – Glenn Bergstrøm 11 mins ago

In that case I would recommend to find the last row and the last column. Identify your range and enter the formula in that range in one go as shown below. I have commented the code but if you still have a problem understanding it, feel free to ask :)

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, LastCol As Long
    Dim myFormula As String

    '~~> Set this to the relevant worksheet
    Set ws = Sheet1

    With ws
        '~~> Find last row
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        '~~> Find last column in row 1
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        '~~> Construct your formula
        myFormula = "=Sum(A2:A" & LastRow & ")"

        '~~> Enter formula in the entire range in 1 go
        .Range(.Cells(LastRow + 1, 1), _
               .Cells(LastRow + 1, LastCol)).Formula = myFormula
    End With
End Sub

Screenshot:

How can I iterate through a row, while using the sum formula for the column above in a for-loop?

1 Answers1