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.
What i want is, when i set B = Cells(2, "B") as my code and place 1.xls in that cell, could the VLOOKUP in VBA become
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[B!R4C1:R6733C2,2,FALSE)"
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[1.xls!R4C1:R6733C2,2,FALSE)"
So there exist no "absolute' file name but only the cell address when the filename is placed?
I need the VBA code for this because I got several worksheets for me to lookup and they all have exactly the same format ( so all the criteria for VLOOKUP is the same, except the name). Therefore i need excel to perform the lookup for me according to the new name i typed in the same cell ( B) but not asking me to select the critera again..what can i do?
OR do i need indirect function for that?
i tried =VLOOKUP(G2,INDIRECT("B2!R4C1:R6733C2"),2,FALSE) where R4C1:R6733C2 is the range i want to look up in the file i paste in B2. However, the above code doesnt work even manually using the function.
