Using Variables for R1C1 Index

[Gerald42]

I am trying to use the custom variable "Index" so that I can use this macro on arbitrarily long data sets.

I'm trying to directly add this variable in the FormulaR1C1 notation I used below. Is there some way of referencing it? Below I tried concatenating it with the & sign but that does not work.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/30/2007 by bendej3
'

'
LastRowColA = Range("A65536").End(xlUp).Row
Index = -LastRowColA + 1
Range("A" & (LastRowColA + 1)).Select
ActiveCell.FormulaR1C1 = "Sum"
Range("C" & (LastRowColA + 1)).Select
ActiveCell.FormulaR1C1 = _
"=SUMIF(R["&Index&"]C[-1]:R[-1]C[-1],""All Parameters"",R["&Index&"]C:R[-1]C)"
End Sub

Thanks!

 

[Lewiy]

Couple of things. Firstly, you need to put a space between a string and an “&” sign, so instead of variable&”string”, you would put variable & “string”. Other than that your formula was fine.
Secondly, you should avoid using “Index” as a variable name because it could be confused with the Index property.
Finally, you don’t usually need to use “Select” commands. This will ultimately slow down your code.
Try this (hopefully you can see what I’ve done based on the above nores:

Sub Macro1()
Dim myIndex As Long
myIndex = Range("A65536").End(xlUp).Row + 1
Range("A" & myIndex) = "Sum"
Range("C" & myIndex).FormulaR1C1 = "=SUMIF(R[" & myIndex & "]C[-1]:R[-1]C[-1],""All Parameters"",R[" & myIndex & "]C:R[-1]C)"
End Sub