Application.WorksheetFunction.VLookup Full Path Not Working (From Another Workbook)

[ziv26]

Hi,

I am new to VBA and would like to use a full path for the Application.WorksheetFunction.VLookup method but cannot make it to work. I really googled for answers but cannot find a perfect example so I really need some help. So in summary I just need help with the arg2 part since I was able to make it work with the commented out code Worksheets("Sheet2").Range("A:B").

Here's my code:

    For Each cell In namedRangeCompanySize
        On Error Resume Next
'        lookUpResult = Application.WorksheetFunction.VLookup(cell, _
'              Worksheets("Sheet2").Range("A:B"), 2, 0)

        lookUpResult = Application.WorksheetFunction.VLookup(cell, Workbooks.Open("C:\Users\sngutierrez\Desktop\MAINICHI FILES\01-ALL-IN-ONE-2018IM.xlsm").Sheets("CO_SI").Range("A:B"), 2, 0)

        On Error GoTo 0
        
        If lookUpResult = "" Then
            cell.Interior.Color = 65535
        Else
            cell.Value = lookUpResult
            lookUpResult = ""
        End If

    Next

Thanks in advance.

 

[ziv26]

Hey guys,

I was able to make it work after some trial and errors (and a lot searches). Do you think this code can be improved?

Here's my code:

Sub VL_CompanySize()

    Dim sPath As String, sFile As String
    Dim wb As Workbook
    
    sPath = "C:\Users\sngutierrez\Desktop\MAINICHI FILES"
    sFile = sPath & "01-ALL-IN-ONE-2018IM.xlsm"
    
    On Error Resume Next
    Set wb = Workbooks("01-ALL-IN-ONE-2018IM.xlsm")
    On Error GoTo 0
    
    If wb Is Nothing Then
        Set wb = Workbooks.Open(sFile)
    End If
    
    Dim rngHeaders As Range
    Dim rngHeaderCompanySize As Range
    Dim namedRangeCompanySize As Range
    Dim cell As Range
    Dim lookUpResult

    Const ROW_HEADERS As Integer = 1
    Const HEADER_NAME As String = "Company Size"

    ' Set rngHeaders = Intersect(Worksheets("Sheet1").UsedRange, Worksheets("Sheet1").Rows(ROW_HEADERS))
    Set rngHeaders = Range("A1:Z1")
    Set rngHeaderCompanySize = rngHeaders.Find(HEADER_NAME)

    If rngHeaderCompanySize Is Nothing Then
        MsgBox ("Company Size column not found.")
        Exit Sub
    End If
    
    Set namedRangeCompanySize = Range(rngHeaderCompanySize.Offset(1, 0), rngHeaderCompanySize.End(xlDown))

    For Each cell In namedRangeCompanySize
        On Error Resume Next
        lookUpResult = Application.WorksheetFunction.VLookup(cell, wb.Sheets("SIZE").Range("A:B"), 2, False)
        On Error GoTo 0
        
        If lookUpResult = "" Then
            cell.Interior.Color = 65535
        Else
            cell.Value = lookUpResult
            lookUpResult = ""
        End If
    Next

End Sub

 

[ziv26]

Anyone?