....actually, I would do this:

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

Regards,
KL


"KL" wrote in message ...
Hi,

I guess it is clearly beacuse you are mixing the R1C1 notation (RC[-5]) with A1 notation ($A2:$B38) in one formula. Also you use only one '-sign whereas it should be on both sides of the sheet name (actually with this name they aren't necessary at all as there are no spaces in it). Besides you are explicitly saying it is a R1C1 formula by "Range("M2").FormulaR1C1=". Try this:

Range("M2").FormulaR1C1="=VLOOKUP(RC[-5], lookuptable!R[-3]C1:R[33]C2, 2,0)"

Regards,
KL



<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.