Translate Vlookup to vba code

[Cloud67]

Hi,
I have two workbooks and I need to match the first column of both of their first worksheet(ws1, ws2) first column(A). If match happens then it will return the matching row with 3 columns (A, F, AK) to the first worksheet. i have this Vlookup

=VLOOKUP(A2,'C:\Users\Temp\[Filename.xls]Sheet1'!$A$1:$C$65536,{1,2,3},FALSE)


Above v lookup works if I put the required columns consecutively (A,B, C) but i am not able to code into VBA.
Could you please help me in converting it to vba code,
Here is what I did,

myvar = ws2.Cells(i, 1)

myVLookupResult = WorksheetFunction.VLookup(myvar, 'C:\Users\Temp\Filename.xls}Sheet1'!$A$1:$C$65536,{1,2,3},FALSE)

but it isn't working. The data is big like there are 10000 lines to check, that's why "if and else" statements are not working. I also tried find and match

a = ws1.Cells(Rows.Count, 1).End(xlUp).Row
b = ws2.Cells(Rows.Count, 1).End(xlUp).Row

FindIn = Range("A2:A500").Value

For i = 2 To a

For k = 2 To b
ws1.Activate
cell = Application.Find(What:=Cells(i, 1).Value, After:=ws2.Cells(1, 1), LookIn:=ws2.Cells(k, 1).Value, LookAt:=xlWhole)

If Not cell Is Nothing Then
ws2.Activate
Union(Cells(k, 1), Cells(k, 4), Cells(k, 37)).Copy
ws1.Activate

Cells(i, 6).Select
ActiveSheet.Paste

Exit For
Else
Exit For
End If
Next k, i

I would appreciate the help
Thanks

 

[Trevor_S]

Does this work?

myVLookupResult = WorksheetFunction.VLookup(myvar,Workbooks("C:\Users\Temp\Filename.xls").Sheets("Sheet1").Range("$A$1:$C$65536"),3,FALSE)

 

[Cloud67]

Does this work?

myVLookupResult = WorksheetFunction.VLookup(myvar,Workbooks("C:\Users\Temp\Filename.xls").Sheets("Sheet1").Range("$A$1:$C$65536"),3,FALSE)

Thanks for trying. It isn't working , giving me error that "Script out of range". Though i come up with solution. Following code take around 2 mins to execute for 10000 lines.

 rng1 = ws1.Range("A2", ws1.Range("A" & ws1.Cells(Rows.Count, 1).End(xlUp).Row)).Value
 rng2 = ws2.Range("A1", ws2.Range("A" & ws2.Cells(Rows.Count, 1).End(xlUp).Row)).Value
    

For x = 1 To UBound(rng2, 1)
For i = 1 To UBound(rng1, 1)
If rng2(x, 1) = rng1(i, 1) Then
ws2.Activate
Union(Cells(x, 1), Cells(x, 4), Cells(x, 37)).Copy
ws1.Activate
    
Cells(i + 1, 7).Select
ActiveSheet.Paste
        
GoTo Nextone
                
                
            
End If
Next i
Nextone:
Next x