VB script for "dynamic" VLOOKUP

[gessing]

Hi,
I am new to VBA and appreciate your help

In directory D:\SourceFile there are a great number of source files.
In my Summary spreadsheet the values in column B indicate the source file name(=number.xls) that is needed.
I would like to place my cursor in a cell in column C and start a macro that will do the following: (1) Lookup the value from the cell in column A (2) in the sourcefile with the name that is in the cell in column B. [the source files are closed and working with indirect.ext does not work]

What I have so far is not working:

Sub PullFromSourceFile()
'
' Keyboard Shortcut: Ctrl+i
'
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'D:\SourceFile\["B2".xls]PriceInfo'!R2C2:R15000C5,2,0)"

End Sub

[TMS]

Try:

Please Login or Register  to view this content.

Regards