VBA实现Vlookup匹配/不匹配返回指定值的代码修改咨询

完整可运行代码 #

Sub 自定义Vlookup匹配()
    Dim goalasRK As Worksheet, dataRK As Worksheet
    Dim goalasLastRow As Long, dataLastRow As Long, X As Long
    Dim DataRng As Range
    Dim lookupResult As Variant
    
    Set goalasRK = ThisWorkbook.Worksheets("AddNewData")
    Set dataRK = ThisWorkbook.Worksheets("Open")
    ' 优化最后一行计算逻辑，避免空行导致计算错误
    goalasLastRow = goalasRK.Range("B" & Rows.Count).End(xlUp).Row
    dataLastRow = dataRK.Range("A" & Rows.Count).End(xlUp).Row
    
    Set DataRng = dataRK.Range("B2:B" & dataLastRow)
    
    For X = 8 To goalasLastRow
        ' 用Application.VLookup不会抛出运行时错误，直接返回错误值方便判断
        lookupResult = Application.VLookup(goalasRK.Range("H" & X).Value, DataRng, 1, False)
        
        If IsError(lookupResult) Then
            ' 匹配失败逻辑
            goalasRK.Range("AK" & X).Value = "Not Found"
            goalasRK.Range("AK" & X).Interior.Color = vbRed
        Else
            ' 匹配成功逻辑
            goalasRK.Range("AK" & X).Value = "Match"
            ' 清除可能存在的红底残留
            goalasRK.Range("AK" & X).Interior.ColorIndex = xlColorIndexNone
        End If
    Next X
End Sub

关键修改点 #

• 匹配成功逻辑补全：VLookup查询命中后，直接给对应单元格赋值为"Match"，同时清空单元格背景色，避免之前操作残留的红底影响显示
• 错误判断逻辑优化：替换原Application.WorksheetFunction.VLookup为Application.VLookup，查询失败时不会触发运行时错误，而是直接返回错误值，配合IsError函数判断更直观，无需使用跳转语句，代码可读性更高
• 边界计算优化：原代码中goalasLastRow使用End(xlDown)计算，若B列8行以下存在空行，会导致统计的最后一行位置偏小，修改为从表格最后一行向上查找的End(xlUp)逻辑，适配更多数据场景

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