解决动态范围场景下WorksheetFunction.VLookup触发1004错误问题

报错核心原因 #

• VLookup函数单次仅支持传入单个值作为查询参数，原代码直接传入整列A列的Range对象作为查询值，不符合函数参数要求
• 未处理匹配失败场景：当查询值为空、或不存在于Database工作表的C列时，WorksheetFunction.VLookup会直接抛出1004错误；仅替换为Application.VLookup没有配套的错误判断逻辑，依然无法正常运行
• 直接使用整列作为操作范围，既会产生不必要的性能消耗，也会放大空白单元格带来的匹配失败问题

修正后代码 #

Sub 核对客户名单()
    Dim lastRow As Long
    Dim i As Long
    Dim Table_Range As Range
    Dim LookupValue As Variant
    Dim FinalResult As Variant
    
    ' 定义查询区域，固定为Database的C:H列
    Set Table_Range = Sheets("Database").Range("C:H")
    ' 动态获取Tool表A列最后一行有数据的行号，仅处理有效数据
    lastRow = Sheets("Tool").Cells(Rows.Count, "A").End(xlUp).Row
    
    ' 循环逐行处理
    For i = 1 To lastRow
        LookupValue = Sheets("Tool").Cells(i, "A").Value
        ' 跳过A列空白行
        If Not IsEmpty(LookupValue) Then
            FinalResult = Application.VLookup(LookupValue, Table_Range, 6, False)
            ' 判断是否匹配成功，匹配失败返回自定义提示
            If IsError(FinalResult) Then
                Sheets("Tool").Cells(i, "B").Value = "未找到匹配客户"
            Else
                Sheets("Tool").Cells(i, "B").Value = FinalResult
            End If
        Else
            ' A列为空时B列清空
            Sheets("Tool").Cells(i, "B").Value = ""
        End If
    Next i
End Sub

调整说明 #

• 新增动态取有效行逻辑，无需处理整列空白单元格，运行效率大幅提升
• 逐行传入单个查询值调用VLookup，符合函数参数要求
• 新增匹配结果判断逻辑，匹配失败时不会触发运行时错误，可自定义返回提示内容
• 新增空白查询值跳过逻辑，避免无效查询

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