VBA - Vlookup help - Data in another workbook

[Malleshg24]

Hi Team,
I am comfortable in applying vba-vlookup formula in same workbook.But when the data is in another workbook i face a problem,
I am trying to apply vlookup up formula in thisworkbook.sheets(2).range("B2")
Mydata is in another workbook. Getting error at vlook up formula.

When searched on google, coming across one sample code,

myRangeName = Range("A1").CurrentRegion.Address
Windows("Book1.xlsm").Activate
 Range("B1").Formula = "=VLOOKUP(A1,[" & myFileName & "]" & mySheetName & "!" & myRangeName & ",2,0)"

Sub Myvlookuptesting_Code()
Dim wbk As Workbook
Set wbk = Workbooks.Open(ThisWorkbook.Worksheets("sheet1").Range("b5").Value)
Dim ws As Worksheet
Set ws = wbk.Worksheets("sheet1")
Dim myrange As Range
Set myrange = ws.Range("a1").CurrentRegion

ThisWorkbook.Activate

ThisWorkbook.Sheets("sheet2").Range("B2").Formula = "=vlookup(A2,"[" & wbk.Name & "]" & ws.Name & "!" & myrange & ",2,0)"
ThisWorkbook.Sheets("sheet2").Range("B2").Formula = "=vlookup(A2,"[" & wbk.Name & "]" & ws.Name & "!" & myrange & ",2,0)"

End Sub

Regards,
Mallesh

[LEFT][FONT=Verdana][COLOR=rgb(20, 20, 20)]

[/COLOR][/FONT][/LEFT]

 

[Marc L]

Hi !

Just compare with a working cell formula !

A sample as a starter :​

Range("Sheet2!B2").Formula = "=VLOOKUP(A2," & myrange.Address(External:=True) & ",2,0)"

Do you like it ? So thanks to click on bottom right Like !
​