I'd use something like:

Dim DataRng as range
dim wb2 as workbook
set wb2 = workbooks.open(....)
with wb2.worksheets("someworksheetnamehere")
set datarng = .range("A2:AL65536")
end with

'make sure you're on the correct cell.

ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-12]," _
& datarng.address(external:=true, ReferenceStyle:=xlR1C1) _
& ",3,FALSE)"





Orhan wrote:

Hi,

On H8 cell i have the value "data"
I managed to open the workbook named "data" using Range("H8")
Workbook.open function.

Set Wb2 = Workbooks.Open("c:\data\" & Range("H8") & ".xls")

How can I do the same thing in a vlookup formula?

ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-12],data.xls!R2C1:R65008C38,3,FALSE)"

Thanks!

--

Dave Peterson