I have a macro that I am trying to do a lookup of a value
in the first row then return the total for that column if
I have a match. The number of rows may change each time I
run the report. I know how to retrieve the number of rows
in the table, but how do I plug that value into my lookup
statement?

Here is my code:

ActiveCell.FormulaR1C1 = _
"=IF(EXACT(R[-2]C,LOOKUP(R[-2]C,'1st Auto
Store'!R1C5:R1C9)),LOOKUP(R[-2]C,'1st Auto Store'!
R1C5:R1C9,'1st Auto Store'!R[1833]C5:R[1833]C9),0)"

The value 1833 is "hard coded" into the macro, but that
number needs to change each time the macro is run. I have
a variable which contains the number of rows.