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.

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

--
Regards,
Tom Ogilvy

<a> wrote in message news:[email protected]...
> 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.
>