Need something like the following



'With Worksheets("Sheet Name")
'set the table for the data you wish to lookup Every time this is run it
should find the last row in column 9
Set Data_TABLE = Range(.Cells(5,1 ), .Cells(Rows.Count, 9).End(xlUp))
'.Select

'the information you wish to find (range "a1")
Find_Value= .cells(1,1) 'a1

'Result is the value to be returned
'explination of VLookup(find thisvalue, in this table, column to lookup,
match type)
Result = Application.VLookup(Find_Value, DATA_Table, 5, 0)

'paste result into ("b1")
..Cells(1,2) = Result

End With





"Marcotte A" wrote in message
...
I am writing code to add a vlookup function to a column of cells. Each
time
the macro is run (because data is added to the spreadsheet), the lookup
table
changes in size. The columns and start row don't change, but the end row
may
increase. I have a variable LastRow that holds the row number of the end
of
the table. How do I incorporate that into my code? Usinge the recorder,
I'm
starting with this - I want to change "Sheet4!R2C1:R113C4" to something
like
"Sheet4!R2C1:R'LastRow'C4"

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet4!R2C1:R113C4,4,0)"