Calling variable file name

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

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.
 
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 said:
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
Click to expand...


.
Click to expand...
 
Back
Top