VLOOKUP功能VBA代码错误处理优化求助：自定义错误返回值

Hey there! Since you're an Excel formula pro but new to VBA, let's break down what's going wrong with your code and fix it step by step.

Your current code uses On Error Resume Next which skips over errors entirely—so when WorksheetFunction.VLookup can't find a value, it throws an error, and the code just moves on without writing anything to Sheet3, leaving cells empty. That's why you're only getting blank values right now. We need to explicitly handle both your error scenarios instead of ignoring errors.

Fixed VBA Code #

Sub VLookupWithErrorHandling()
    Dim cl As Range
    Dim Table1 As Range
    Dim Table2 As Range
    Dim Fund_Row As Long
    Dim lookupResult As Variant ' Holds either the match result or error value
    
    ' Define your ranges (adjust these to match your actual data!)
    Set Table1 = Sheets("Sheet1").Range("A2:A100") ' Example: Column A on Sheet1
    Set Table2 = Sheets("Sheet2").Range("B2:B200") ' Example: Column B on Sheet2
    
    Fund_Row = Sheets("Sheet3").Range("B2").Row ' Start writing at Sheet3's B2
    
    For Each cl In Table1
        ' Scenario 1: Lookup cell is empty
        If IsEmpty(cl.Value) Then
            Sheets("Sheet3").Cells(Fund_Row, 2).Value = ""
        Else
            ' Use Application.VLookup instead of WorksheetFunction.VLookup
            ' This returns an error value (like #N/A) instead of crashing
            lookupResult = Application.VLookup(cl.Value, Table2, 1, False)
            
            ' Scenario 2: Value not found in lookup range
            If IsError(lookupResult) Then
                Sheets("Sheet3").Cells(Fund_Row, 2).Value = "Missing"
            Else
                ' Value found, write the matched result
                Sheets("Sheet3").Cells(Fund_Row, 2).Value = lookupResult
            End If
        End If
        Fund_Row = Fund_Row + 1 ' Move to the next row for the next lookup
    Next cl
End Sub

Key Improvements Explained #

• Switched to Application.VLookup: Unlike WorksheetFunction.VLookup, this version doesn't throw a runtime error when a value isn't found—it returns an error value (similar to Excel's #N/A). This lets us safely check for missing values with IsError().
• Explicit empty cell check: We first verify if the source cell (cl) is empty, and write a blank to Sheet3 immediately, matching your first requirement.
• Clear error handling for missing values: For non-empty cells, we check if the VLookup result is an error. If yes, we write "Missing"; otherwise, we write the matched value.
• Proper range setup: I added Set statements for Table1 and Table2 (your original code referenced rangeA/rangeB—make sure to adjust these ranges to your actual data columns!).

Think of this as the VBA equivalent of the Excel formula:
=IF(ISBLANK(A2),"",IF(ISNA(VLOOKUP(A2,Sheet2!B:B,1,FALSE)),"Missing",VLOOKUP(A2,Sheet2!B:B,1,FALSE)))

内容的提问来源于stack exchange，提问作者reece