VLOOKUP according to location not NAME

[Solved/Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
i put the name of the files i want to perform in some cells and wrote a maco for excel to call up the file according to the location. (lets say I write 1.xls in cells Cells(2, "B"))
e.g.
A = Cells(1, "B")
B = Cells(2, "B")
C = Cells(3, "B")
Workbooks.Open Filename:=A
Windows(A).Activate

Now, i want to do VLOOKUP by looking up "B" (which is cells(2,"B") i.e. 1.xls)

from the macro i record, it becomes :

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[1.xls!R4C1:R6733C2,2,FALSE)"

However, since i will change the name of the file i want to lookup in ther future, i do NOT want the phase "1.xls" to appear in the formula... but only the location (i.e B) for excel to look up.

Please help. Thanks!!

2 replies

To be more specific, how can i change the code so that excel can look up the data i want according to the file names( that i would change in the future) i put in the specific cells' location (e.g. cells (2, "B")) ,but not the "absolute" filename? Thanks.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
and how would the file name change ?
Right now you are using a lookup that looks up the value in some other book. You always have to code for that workbook name, else you would be looking in the same workbook
workbook name -- sheet name -- cell address is how the lookup works

if workbook name is skipped, active workbook is assumed
if sheet name is skipped, then active sheet is assumed
i tried the code this morning and it still gave the error 1004: Application-defined or object-defined error ... however, when i put the cursor on the code, it could show the file (2.xls) which is exactly the file i want to have the lookup. So for same reason, excel just cant interpret the whole code... please help! Thanks.
Thank you so much! it works now!!
It seems like i have to change "Sheet1" into "sheet1".. even though the tab name is actually "Sheet 1".. anyway thank you very much for your help!
I don't understand what happen to the code. I placed exactly the same code to another file with exactly the same format, the code simply doesn't work and give error 1004: Application-defined or object-defined error .

this is the code ,where B2 is the cell with the file name.
Range("G3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[2],'[" & Range("B2") & "]sheet1'!R4C1:R6733C2,2,FALSE)"

I am desperate to finish this task... please help! Thanks.
hi rizvisa1,
I made a very silly mistake.. and the code is now working properly. Still, thank you very much for you help!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
great. thanks for the feed back