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

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

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