Cells(i, "E").FormulaR1C1 = _
"=VLOOKUP(RC1,'[Consolidated " & _
Range("Sheet1!A1").Value & _
".xls]Sheet1'!R2C3:R36C21,18,0)"

If you are going to use FormulaR1C1, all your references need to be in R1C1
notation.

If you are going to return the value from column 18 in the lookup range, you
need to have a lookup range that is at least 18 columns wide.

--
Regards,
Tom Ogilvy



"Michael" wrote in message
...
I have a macro that inserts a vlookup referencing from
another file, the problem i am having is that the
reference file has a different name each time the macro is
run. The reference file name is "Consolidted" followed by
the creation date.
Cells(i, "E").FormulaR1C1 = _
"=VLOOKUP(RC1,'[Consolidated 120903.xls]Sheet1'!
C2:C36,18,0)"

The creation date is referenced on "Sheet1" range "A1" of
the Consolidated file. I have tried using a "MyFile" name
but can not make it work. Any help would be greatly
appreciated

Thanks
Michael