VBA Lookup array

[mattless1]

Hi All,

im looking for a little help if possible.

i have a code below which, looks for the range in column A Sheet2 and then looks for a range in sheet1 main data and returns a specified column from sheet1. this works fine, but i'd like it to look for the range in another workbook. and return a column from the other workbook.. an alternative to Xlookup.

this works fine on a single workbook.

Sub macroLookup()

    ' Disable these Excel properties whilst macro runs
    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
   
        ' Declare object variables for macro
        Dim lastRowIn, lastRowOut, i As Long
        Dim lookFor, j, inArray, outArray, findArray As Variant
        Dim inWks, outWks As Worksheet
       
            ' Set certain object variables
            Set inWks = ThisWorkbook.Sheets(1)  ' source input sheet with data
            Set outWks = ThisWorkbook.Sheets(2)  ' output sheet with specific values to find
           
                ' Find the last rows in the source & output fields of company names
                lastRowIn = inWks.Cells(Rows.Count, "A").End(xlUp).Row
                lastRowOut = outWks.Cells(Rows.Count, "D").End(xlUp).Row
               
                    ' Record the input array of data from A1
                    inArray = Range(inWks.Cells(1, 1), inWks.Cells(lastRowIn, 16))
                   
                    ' Load the find array from the output column in col D in Sheet2
                    findArray = Range(outWks.Cells(1, 4), outWks.Cells(lastRowOut, 4))
                   
                    ' Define the output array in col E in Sheet2
                    outArray = Range(outWks.Cells(1, 5), outWks.Cells(lastRowOut, 5))
                   
                        ' Error handler
                        On Error Resume Next
                       
                        ' First loop through the output names in col D, sheet2
                        For i = 2 To lastRowOut
                       
                            ' The second loop through the input names in col A, sheet1
                            For j = 2 To lastRowIn
                           
                                lookFor = findArray(i, 1)
                               
                                If inArray(j, 1) = lookFor Then
                                    outArray(i, 1) = inArray(j, 16)
                                    Exit For
                                End If
                               
                            ' move to next value in col A, sheet1
                            Next j
                   
                        ' move to next value in col D, sheet2
                        Next i
                       
            ' write out the output array values in col E, sheet2
            Range(outWks.Cells(1, 2), outWks.Cells(lastRowOut, 2)) = outArray
           
    ' Re-enable Excel properties as macro is complete & finished running
    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

 

[Skyybot]

You can get a range from another Workbook with the Application with the following

Dim wb as WorkBook
wb = Application.Workbooks.Open("C:\Users\User\Folder\File.ext")

 

[mattless1]

Thank you i'll give it a try.