Loop through a range populating with a dynamic formula to sum a row

[waltdakind]

Hi All,
I hope that I'm just missing something simple here -- I can't get the following code to work.

Sub sumfornondddgrid()
Dim lastrow As Integer
Dim lastcolumn As Integer

Sheets("02Grid by Dept and GLCode").Activate
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column

For i = lastrow To 3 Step -1

Range(Cells(i, lastcolumn)).Offset(0,1).Formula="=SUM(" & Cells(i, 4), Cells(i, lastcolumn)) & ")"
Next i

End Sub

It's supposed to loop through the range and populate the cells in the range with the sum of the range varying on the size of the grid. I tried adding the "Address" property to cells, but that didn't work either. What am I missing?


Thanks for whatever help you can give!

 

[NeonRedSharpie]

Sub sumfornondddgrid()    Dim lastrow As Integer
    Dim lastcolumn As Integer
    
    
    With Sheets("02Grid by Dept and GLCode")
    
        lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
        lastcolumn = .Cells(1, Columns.Count).End(xlToLeft).Column
        
        Range(.Cells(3, lastcolumn), .Cells(lastrow, lastcolumn)).Offset(0, 1).FormulaR1C1 = "=SUM(RC4:RC[-1])"
    
    End With
End Sub

 

[waltdakind]

Thanks! That worked like a charm.

I'm trying to make sense of the syntax so I understand how it works though. Using the RC reference RC[-1] returns the cell to the left of the active cell in the range?

 

[NeonRedSharpie]

There are a few options for RC.

R[x]C[x]

That will be a relative reference. So R[1]C is the row + 1. RC[1] is the column +1.

RxCx

That will be a mixed or absolute reference. R1C1 will return A1. R10C10 will return J10.

 

[waltdakind]

Thanks for the prompt reply and the explanation. Extremely helpful!