use a variable in a formula

[Guest]

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)"

 

[David Adamson]

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

 

[DavidC]

First you need to find the row number for the last row in
which your data appears.

To find the last row:

rn = Worksheets("name of your sheet").Range("A65536").End
(xlUp).Row

This then assigns the row number to a variable 'rn'. The
formula works by going to the very last row and then going
up to the first row with data in it using column A as the
reference column.

Now amend your formula:

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

This should then return the formula with the correct row
number.

BOL
DavidC