VLOOKUP from another sheet VBA programming

[Guest]

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.

 

[Tom Ogilvy]

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