rschmidt46
New Member
- Joined
- Aug 24, 2015
- Messages
- 46
- Office Version
- 2010
- Platform
- Windows
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?
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?