VBA vlookup from another file in another folder

[lonely_hommie]

I'm trying to use data from another folder for my vlookup, but I the code below can't find the file. Anytips?

Set table_array_Sales_Continent_New = Workbooks.Open("C:\Users\xxx\Documents\Mapping\Mapping.xlsx").Sheets("Mapping").Range("O2:P50")

 

[Fluff]

Hi & welcome to MrExcel
If you are trying to open the file it should be

Set table_array_Sales_Continent_New = Workbooks.Open("C:\Users\xxx\Documents\Mapping\Mapping.xlsx")

 

[lonely_hommie]

Hi, thanks for helping. I am not only trying to open the file, but also use the file and the called "Mapping" within the file and use range from O2:P50 as a vlookup.

 

[Fluff]

Can you post the rest of your code?

 

[lonely_hommie]

Thank you for helping. I hope my code makes sense.

Option Explicit
Sub TRR_Mapping_VBA()
'Application.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

'=IFERROR(VLOOKUP(AF2,Mapping!O:P,2,FALSE),"GTS Misc")
'Function I used in Excel

    Dim lookfor As Range 'lookfor
    Dim table_array As Range 'table_array
    Dim varResult As Variant 'varResult
    Dim table_array_col As Integer 'table_array_col
    Dim lookFor_col As Integer 'lookFor_col
    
    Set lookfor = Range([AF2], Cells(Rows.Count, "AF").End(3))
    
    Set table_array = Workbooks.Open("C:\Users\xxx\Documents\Mapping\Mapping.xlsx").Sheets("Mapping").Range("O2:P50")
    
    table_array_col = 2  'pull data on this column
    
    varResult = Application.VLookup(lookfor.Value, table_array, table_array_col, 0)
    
    lookFor_col = 24  'lookFor.Value starting from 0/1/2/3 where to start from column
    lookfor.Offset(0, lookFor_col) = varResult
    
     MsgBox "Done"
    
End Sub

 

[Fluff]

Try

Sub TRR_Mapping_VBA()
'Application.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

'=IFERROR(VLOOKUP(AF2,Mapping!O:P,2,FALSE),"GTS Misc")
'Function I used in Excel

    Dim lookfor As Range 'lookfor
    Dim table_array As Range 'table_array
    Dim varResult As Variant 'varResult
    Dim table_array_col As Integer 'table_array_col
    Dim lookFor_col As Integer 'lookFor_col
    Dim Wbk As Workbook
    
    Set lookfor = Range("AF2")
    
    Set Wbk = Workbooks.Open("C:\Users\xxx\Documents\Mapping\Mapping.xlsx")
    Set table_array = Wbk.Sheets("Mapping").Range("O2:P50")
    
    table_array_col = 2  'pull data on this column
    
    varResult = Application.vlookup(lookfor.Value, table_array, table_array_col, 0)
    
    lookFor_col = 24  'lookFor.Value starting from 0/1/2/3 where to start from column
    lookfor.Offset(0, lookFor_col) = varResult
    
     MsgBox "Done"
    
End Sub

 

[lonely_hommie]

It Works! Had to change the below to make sure I get vlookup for all the rows.
Set lookfor = Range([AF2], Cells(Rows.Count, "AF").End(3))

Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback