VLOOKUP from another sheet VBA programming
 

hi,

I have 2 sheets in my workbook,

sheet 1 contains the main analysis of my data

sheet 2 contains a table which I will reference with VLOOKUP from sheet 1


I have written the following VBA code for my purpose:
Worksheets(1).Activate //activate sheet 1
Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-5], lookuptable'!$A2:$B38, 2,
false)" //input into cell M2 of sheet 1 this formula.

when I run my code,
cell M2 in sheet 1 will give a #NAME? error and when I look at the formula
in the cell:
=VLOOKUP(H2,personal.xls!'A2':'B38',2,FALSE)

notice the extra single quotes around A2 and B38.

ps: i defined my module in personal.xls to be able to access it from any
workbook.

thnks.
Michael.

VLOOKUP from another sheet VBA programming
 

You can't mix R1C1 notation and A1 notation in a formula such as you have.

Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-5], lookuptable!R2C1:R38C2,
2,false)"

--
Regards,
Tom Ogilvy

<a wrote in message ...
hi,

I have 2 sheets in my workbook,

sheet 1 contains the main analysis of my data

sheet 2 contains a table which I will reference with VLOOKUP from sheet 1


I have written the following VBA code for my purpose:
Worksheets(1).Activate //activate sheet 1
Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-5], lookuptable'!$A2:$B38, 2,
false)" //input into cell M2 of sheet 1 this formula.

when I run my code,
cell M2 in sheet 1 will give a #NAME? error and when I look at the
formula
in the cell:
=VLOOKUP(H2,personal.xls!'A2':'B38',2,FALSE)

notice the extra single quotes around A2 and B38.

ps: i defined my module in personal.xls to be able to access it from any
workbook.

thnks.
Michael.