Using a Variable in a Formula in R1C1 Style in VBA

[rschmidt46]

I cannot get the following code to work and I do not know why:

Sub Count_Records()
Dim LCol, LRow, Row_Diff, X As Integer
LRow = [A1].End(xlDown).Row + 1
Row_Diff = 2 - LRow
Cells(LRow, 1).FormulaR1C1 = "=COUNTA(R[" & [Row_Diff] & "]C:R[-1]C"
End

I receive a weekly report with a different number of rows most times. I'm trying to enter a "COUNTA" formula in the row below the last one with data using VBA. The formula needs to reflect the difference between row 2 and the last row with data (variable "LRow"). I thought defining the difference with another variable "Row_Diff" would work but the line of code errors out. Can someone spot what's wrong with the syntax or if a variable cannot be used like this?

 

[Fluff]

There is no need for the Row_Diff variable, you can use

Cells(LRow, 1).FormulaR1C1 = "=COUNTA(R2C:R[-1]C)"

 

[rschmidt46]

Fluff,
Wow, that was simple, although I apparently don't know how R1C1 works and rarely use it! Seems like a better way of coding many of the things I regularly encounter.
Thanks,
Randy

 

[Fluff]

You're welcome & thanks for the feedback.