如何为VBA中的VLOOKUP添加错误检查

问题分析 #

你需要将Excel公式迁移至VBA，实现**两列值均存在于目标区域则返回“已审核”，否则返回“未审核”**的逻辑，当前代码在待检查单元格为空或值不存在时触发1004运行时错误，核心问题是缺少空值检查和错误捕获机制，且未实现多列检查逻辑。

修改后的完整代码 #

Sub CheckRoomAudited_Method()
    Dim AuditCheckCell As Range
    Dim TotaltoCheck As Long
    Dim EndofAuditRange As Long
    Dim AuditCheckColumn As Range
    Dim VlookupValue1 As Variant ' 第一列待检查值
    Dim VlookupValue2 As Variant ' 第二列待检查值
    Dim TableArrayRange As Range
    Dim LookupResult1 As Variant
    Dim LookupResult2 As Variant
    
    ' 获取最后一行（避免因空行导致的范围截断，改用xlUp更可靠）
    With Worksheets("RoomUse")
        TotaltoCheck = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set AuditCheckColumn = .Range(.Cells(2, 23), .Cells(TotaltoCheck, 23)) ' W列，存放结果
    End With
    
    With Worksheets("auditdata")
        EndofAuditRange = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set TableArrayRange = .Range(.Cells(1, 1), .Cells(EndofAuditRange, 4)) ' A:D区域
    End With
    
    For Each AuditCheckCell In AuditCheckColumn
        ' 获取两列待检查值（示例：AK列(37)和AJ列(36)，可根据实际需求调整Offset列数）
        VlookupValue1 = AuditCheckCell.Offset(0, 14).Value ' AK列：W列偏移14列（37-23=14）
        VlookupValue2 = AuditCheckCell.Offset(0, 13).Value ' AJ列：W列偏移13列（36-23=13）
        
        ' 前置检查：任一待检查单元格为空，直接返回未审核
        If VlookupValue1 = "" Or VlookupValue2 = "" Then
            AuditCheckCell.Value = "未审核"
            GoTo NextCell ' 跳过后续检查
        End If
        
        ' 使用Application.VLookup（出错时返回错误值，而非抛出运行时错误）
        LookupResult1 = Application.VLookup(VlookupValue1, TableArrayRange, 1, False)
        LookupResult2 = Application.VLookup(VlookupValue2, TableArrayRange, 2, False) ' 检查第二列，对应auditdata的B列
        
        ' 判断两个值是否都存在
        If Not IsError(LookupResult1) And Not IsError(LookupResult2) Then
            AuditCheckCell.Value = "已审核"
        Else
            AuditCheckCell.Value = "未审核"
        End If
        
NextCell:
    Next AuditCheckCell
End Sub

关键优化点 #

• 可靠的最后一行获取：改用Cells(Rows.Count, "A").End(xlUp).Row替代Range("A2").End(xlDown).Row，避免因中间空行导致范围截断。
• 空值前置检查：提前判断待检查单元格是否为空，避免无效的VLookup调用。
• 错误友好的VLookup调用：使用Application.VLookup而非WorksheetFunction.VLookup，前者找不到值时返回#N/A错误值，可通过IsError()判断，不会抛出1004错误。
• 多列检查逻辑：实现两列值的同时存在性验证，可根据实际需求调整VLookup的列索引（第三个参数）和待检查列的Offset偏移量。
• 流程控制：用GoTo NextCell跳过空值的后续处理，提升代码效率。

另一种错误处理方式（On Error Resume Next） #

如果你更习惯用错误捕获语句，也可以采用以下写法：

For Each AuditCheckCell In AuditCheckColumn
    VlookupValue1 = AuditCheckCell.Offset(0, 14).Value
    VlookupValue2 = AuditCheckCell.Offset(0, 13).Value
    
    If VlookupValue1 = "" Or VlookupValue2 = "" Then
        AuditCheckCell.Value = "未审核"
        GoTo NextCell
    End If
    
    ' 启用错误捕获
    On Error Resume Next
    LookupResult1 = WorksheetFunction.VLookup(VlookupValue1, TableArrayRange, 1, False)
    LookupResult2 = WorksheetFunction.VLookup(VlookupValue2, TableArrayRange, 2, False)
    
    ' 判断是否出错
    If Err.Number = 0 Then
        AuditCheckCell.Value = "已审核"
    Else
        AuditCheckCell.Value = "未审核"
    End If
    On Error GoTo 0 ' 重置错误捕获
    
NextCell:
Next AuditCheckCell

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