VBA Vlookup find value using VLookup

0
votes

I am trying to create a macro that loops through every row in worksheet named "source". It need to look for a matching value in named range "OverheadResults".

When I test, I get

error 1004 "Method 'Range' of object '_Worksheet' failed

when I try to use the vlookup (scroll down it's inside the 'Main' loop)

Private Const SOURCE_SHEETNAME = "source"
Private Const COLUMN_WBS = 2
Private Const COLUMN_COSTELEM = 9
Private Const COLUMN_COST = 12
Private Const COLUMN_HOURS = 15
Private Const OVERHEAD_LOOKUP_RANGE_NAME = "OverheadResults"

Sub ConvertSAPActuals()
    Dim iLastRow As Long
    Dim iDestRow As Long
    Dim wb As Workbook
    Dim wbDest As Workbook
    Dim shtSource As Worksheet
    Dim shtDest As Worksheet
    Dim sCostElem As String
    Dim result As Variant


    '--make sure source sheet exists
    If Not SheetExists(SOURCE_SHEETNAME) Then
        MsgBox "Missing source sheet", vbCritical
        Exit Sub
    End If

    Set wb = ThisWorkbook
    Set shtSource = wb.Sheets(SOURCE_SHEETNAME)

    '--create destination workbook and sheet
    Set wbDest = Workbooks.Add
    Set shtDest = wbDest.Sheets(1)

   '--Find the last row (in column A) with data.
    iLastRow = shtSource.Range("A:A").Find("*", searchdirection:=xlPrevious).Row

    '--Main Loop
    iDestRow = 1
    For iSourceSheetRow = 2 To iLastRow
        sCostElem = shtSource.Cells(iSourceSheetRow, COLUMN_COSTELEM)
        result = Application.WorksheetFunction.VLookup(sCostElem, shtSource.Range(OVERHEAD_LOOKUP_RANGE_NAME), 2, False)

        '--Check return value of Vlookup
        If IsError(result) Then
        Else
        End If

    Next

End Sub
2
excelvba

2 Answers

1
votes

While it seems counter-intuitive, you cannot catch a worksheet error (e.g. #N/A, #REF!, etc) thrown by Application.WorksheetFunction.VLookup or WorksheetFunction.VLookup into a variant for examin ation with IsError.

You need to use Application.VLookup instead.

    Dim result As Variant

    '...

    result = Application.VLookup(sCostElem, shtSource.Range(OVERHEAD_LOOKUP_RANGE_NAME), 2, False)

    '--Check return value of Vlookup
    If IsError(result) Then
        debug.print "error: " & result
    Else
        debug.print "match: " & result
    End If
0
votes

Minor Error Handling

If you don't want to change your code you can use this:

    On Error Resume Next
    result = Application.WorksheetFunction.VLookup(sCostElem, _
            shtSource.Range(OVERHEAD_LOOKUP_RANGE_NAME), 2, False)
    '--Check return value of Vlookup
    If Err Then
        ' Reset error.
        On Error GoTo 0
      Else
'***********************************************************
    ' IMPORTANT CORRECTION:
    ' Added the following line for reasons Pᴇʜ pointed out in the comments.
        ' Reset error.
        On Error GoTo 0
'***********************************************************
        ' Do stuff with 'result' e.g.
        Debug.Print result
    End If