Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB formula referencing other worksheet
Hi
I have a macro that fills in some data, and makes the last cell in the row the active cell. I want to use the vlookup function to get a calculated value for the last cell of that row. I don't seem to be able to correctly reference the lookup table (in another worksheet in the same workbook, and labelled as RVU Lookup) ActiveCell.FormulaR1C1 = "CMH" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "No" rvu = ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = VLOOKUP(rvu,'RVU Lookup'!A3:B7213,2,FALSE) doesn't work Thanks in advance CLS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB formula referencing other worksheet
ActiveCell.FormulaR1C1 = "CMH"
ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "No" rvu = ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=VLOOKUP(" & _ rvu & ",'RVU Lookup'!A3:B7213,2,FALSE)" -- Regards, Tom Ogilvy wrote in message oups.com... Hi I have a macro that fills in some data, and makes the last cell in the row the active cell. I want to use the vlookup function to get a calculated value for the last cell of that row. I don't seem to be able to correctly reference the lookup table (in another worksheet in the same workbook, and labelled as RVU Lookup) ActiveCell.FormulaR1C1 = "CMH" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "No" rvu = ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = VLOOKUP(rvu,'RVU Lookup'!A3:B7213,2,FALSE) doesn't work Thanks in advance CLS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB formula referencing other worksheet
Thanks for your help -
I'm still not getting the desired result, because of an error in my original formula: What I want is to have 'this' be the active cell into which the vlookup formula is placed ActiveCell.FormulaR1C1 = "CMH" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "No" this = ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=VLOOKUP(" & _ rvu & ",'RVU Lookup'!A3:B7213,2,FALSE)" and 'rvu' be a relative reference to a cell located 5 columns to the left of the cell with the formula in it My code looks like this: ActiveCell.FormulaR1C1 = "No" ActiveCell.Offset(0, 1).Select rvu = ActiveCell.Offset(0, -5) ActiveCell.FormulaR1C1 = "=VLOOKUP(" & _ rvu & ",'RVU Lookup'!A3:B7213,2,FALSE)" and what I get (in the correct cell at least), is this: =VLOOKUP(54161,'RVU Lookup'!'A3':'B7213',2,FALSE) where 54161 is the value in the cell I want, not the cell (in this case G13) that I want. Thanks again CLS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace worksheet without affecting the referencing formula | Excel Discussion (Misc queries) | |||
CountIF() in Worksheet B while referencing cells in Worksheet A | Excel Worksheet Functions | |||
Same formula referencing same cells returns incorrect results, randomly, when pasted into new worksheet | Excel Worksheet Functions | |||
Formula Referencing Worksheet Name by Variable | Excel Programming | |||
Formula Referencing Worksheet Name by Variable | Excel Programming |