使用Vlookup查找VLookup值

Question

我正在尝试创建一个宏，它循环遍历工作表中的每一行，名为"source“。它需要在命名范围"OverheadResults“中寻找匹配的值。

当我测试时，我得到

错误1004“对象'_Worksheet‘的方法’范围‘失败

当我尝试使用vlookup时(向下滚动它在'Main‘循环中)

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

Answer 1

虽然这似乎有违直觉，但您无法捕捉到Application.WorksheetFunction.VLookup或WorksheetFunction.VLookup抛出的工作表错误(例如#N/A、#REF！等)，以便使用IsError进行检查。

您需要使用Application.VLookup代替。

    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

Answer 2

小错误处理

如果您不想更改代码，可以使用以下方法：

    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