Calling variable file name
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 |
Calling variable file name
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 |
Calling variable file name
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 . |
All times are GMT +1. The time now is 12:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com