put sum formula in variable column for known number of rows

[Resolute]

Hi

i have a sheet with a number of rows , the number of rows, though it can vary is in this instance calculated as 30 (last row 39 first row 9)
the number of columns could vary. (num_cols)
The first column is known i.e "G" or 7

row 9 is the first row and in this instance the last row is 39

i wish to place a sum, using a formula, in a column 2 removed from the last column i.e (num_cols +2)

i want to loop through rows i.e (assuming that's the best method

so i would start in (9,num_cols+2)

' for i = 9 to 39
'****************************************
place sum formula in numcols + 2
'***************************************
next i


so the formula in the first cell would be =SUM(G9:AU9)
next cell would be =SUM(G10:AU10) and so on


if anyone can help with the syntax that would enable me to write the formula in vba using row and column numbers
and incrementing the row as its loops i'd be really grateful

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub resolute()
   Dim UsdCols As Long, Usdrws As Long
   
   UsdCols = Cells(8, Columns.Count).End(xlToLeft).Column
   Usdrws = Range("G" & Rows.Count).End(xlUp).Row
   Range(Cells(9, UsdCols + 2), Cells(Usdrws, UsdCols + 2)).FormulaR1C1 = "=sum(rc7:rc[-2])"
End Sub

 

[Resolute]

Hi & welcome to MrExcel.
How about

Sub resolute()
   Dim UsdCols As Long, Usdrws As Long
 
   UsdCols = Cells(8, Columns.Count).End(xlToLeft).Column
   Usdrws = Range("G" & Rows.Count).End(xlUp).Row
   Range(Cells(9, UsdCols + 2), Cells(Usdrws-2, UsdCols + 2)).FormulaR1C1 = "=sum(rc7:rc[-2])"
End Sub

Brilliant thank you so much works perfectly.

 

[Fluff]

You're welcome & thanks for the feedback.