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

waltdakind

New Member
Joined
May 11, 2010
Messages
30
Hi All,
I hope that I'm just missing something simple here -- I can't get the following code to work.

Code:
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!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Code:
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top