Macro to use the vlookup function in another workbook

[Hernan_g_f]

Hello all,

I need to modify this macro using vlookup function. I have a code where the macro use the vlookup function into the same workbook. And I need to modify it so search in another workbook.

The macro is recorded in a workbook called: AR.xlsm

Sheets("1").Select

Dim cont As Long
Dim ultlinea As Long
Dim O As Variant
Dim H As Variant
Dim L As Variant
Dim C As Variant

Dim Fecha As Variant
Dim Rango As Variant
Set Rango = Sheets("1").Range("E16:DH1400")

ultlinea = Sheets("1").Range("E" & Rows.Count).End(xlUp).Row

For cont = 17 To ultlinea


Fecha = Sheets("1").Cells(cont, 114)
O = Application.VLookup(Fecha, Rango, 3, False)

If IsError(O) Then
O = 0
End If

Sheets("1").Cells(cont, 115) = O
Next cont

End sub

The line Rango is neccesary to modify? How can I change the Range, so the macro search in another workbook?

The workbook that I need to apply the vlookup function is called: FTM.xlsm

Thank you very much!
Hernán

 

[Colo]

Make sure another workbook is opened. Assuming another workbook name is "Book2.xlsx" and, the sheet name is "Sheet1". So code can be written as follows.

Dim wb As Workbook, sh As Worksheet
    Set wb = Workbooks("Book2.xlsx")
    Set sh = wb.Sheets("Sheet1")
    Set Rango = sh.Range("E16:DH1400")