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

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

Tom
Many thanks for your help, all is now working.

Thanks again
Michael
-----Original Message-----
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


.