Changing formula range when looping in VBA

[DSCHROE67]

I am trying to create a loop that will increment the column value in the formula below to to the right each time the loop continues.
In the formula below C4 needs to become C5, C6,... etc.
The formula works when I loop it but it is always providing the subtotal for column 4

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,(OFFSET(R2C4,0,0,COUNTA(C1)-1,1)))"


Below is the code I was trying to use. I was trying to define an amount (AAA) and add 1 to it. The AAA would replace the column reference (starting at 4) in the bold formula below

Sub macro ()
Dim AAA As Integer
AAA = 4

Range("D1").Select
Selection.End(xlDown).Select

Do While ActiveCell <> ""
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,(OFFSET(R2CAAA,0,0,COUNTA(C1)-1,1)))"
ActiveCell.Offset(-2, 1).Select
AAA=AAA+1
Loop

End Sub

Thanks for any input.

 

[NateSC]

Since the formula is a string, you just concatenate using &:

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,(OFFSET(R2C" & AAA & ",0,0,COUNTA(C1)-1,1)))"

 

[DSCHROE67]

Thanks so much for your help. This worked perfect. I new I was on the right track just couldn't remember the correct syntax