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.

Hello,
Have you ever tried the Cells method?
layout: Cells(row,column)
ie: cells(1833,e) or cells(1833,5) or cells(varLastRow,varLastCol)
here is an example: note the rowabsolute effect on the range address.
Second part was for fun. :)

Sub Macro1()
Dim varLastRow, varLastCol As Integer
Dim rgAddress As String
Range("d16").Value = "This is the last cell"
varLastRow = Cells.SpecialCells(xlLastCell).Row
varLastCol = Cells.SpecialCells(xlLastCell).Column
Range(Cells(1, 1), Cells(varLastRow, varLastCol)).Select
rgAddress = Selection.Address(RowAbsolute:=False)
MsgBox (rgAddress)

x = 1
For Each c In Selection
c.Select
With Selection.Interior
.ColorIndex = x
.Pattern = xlSolid
End With
x = Int((36 * Rnd) + 1)
c.Value = x
Next c
Range("e16").Select
End Sub


"Sylvia" wrote in message
...
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.