VLOOKUP according to location not NAME

Question

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.

---

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.

Answer 1

Any reason you couldn't set a starting range and then offset based on the row assigned to the variable?