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.
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.