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!!
P.S. 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.