How to use a variable in R1C1

[rassten]

This does not work:

LR = Cells(Rows.Count, 1).End(xlUp).Row
'Range("B2").FormulaR1C1 = "=COUNTIF(R2C1:R&"LR"&C1,RC[-1])"
Range("B2").Formula = "=COUNTIF($A$2:$A$LR, A2)"
    Selection.AutoFill Destination:=Range("B2:B25")
    Range("B2:B25").Select

But how do use a variable in VBA, R1C1 style

 

[Richard Schollar]

Hi Sten

You had the ampersand and quotes in the wrong order:

'Range("B2").FormulaR1C1 = "=COUNTIF(R2C1:R" & LR & "C1,RC[-1])"

 

[doofusboy]

Have you tried:

Range("B2").Formula = "=COUNTIF($A$2:$A$" & LR & ", A2)"

 

[pgc01]

Notice that you don't need the autofill, you can apply the formula directly to the whole range.

 

[rassten]

Thank you Richard, that was a big help.

For me R1C1-style is hard to understand.

doofusboy, yes, but I but at some point I have to learn R1C1.

pgc01, How?

 

[pgc01]

Hi Sten

Try:

Sub Test()
Dim LR As Long
 
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2:B" & LR).FormulaR1C1 = "=COUNTIF(R2C1:R" & LR & "C1,RC[-1])"
End Sub

 

[rassten]

Thank you,
Thats speeds up the macro significantly.