Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
use a variable in a formula
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)" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
use a variable in a formula
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)" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
use a variable in a formula
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 -----Original 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)" . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DDE formula with variable | Excel Worksheet Functions | |||
variable in a formula | Excel Programming | |||
Variable in a Formula | Excel Programming | |||
Variable in a formula | Excel Programming | |||
Variable to formula. | Excel Programming |