VBA代码无报错无结果：对比两工作表A列并标记不存在项

原代码的核心问题 #

• 未遍历目标行：直接将Sheet1整个A列赋值给valueToFind，Find方法无法处理数组类型的搜索参数，导致匹配逻辑完全失效。
• 无效对象引用：当foundCell Is Nothing时，访问foundCell.Row会触发运行时错误（对象未初始化），你未遇到错误可能是代码未实际执行到该分支，或错误被系统忽略。
• 逻辑覆盖不全：仅执行一次查找操作，未遍历Sheet1的所有数据行，无法为所有符合条件的行添加标记。

修复后的代码（基础遍历版） #

适合数据量较小的场景，逻辑直观：

Sub CheckValues()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lastRow1 As Long
    Dim i As Long
    Dim foundCell As Range
    
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    ' 获取Sheet1 A列实际数据的最后一行，避免遍历空行
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历Sheet1每一行A列数据
    For i = 1 To lastRow1
        ' 在Sheet2 A列查找当前值
        Set foundCell = ws2.Range("A:A").Find(what:=ws1.Cells(i, "A").Value, _
                                            LookIn:=xlValues, _
                                            LookAt:=xlWhole, _
                                            MatchCase:=False)
        
        ' 未找到则标记"Resign"，找到则清空E列（避免旧数据残留）
        If foundCell Is Nothing Then
            ws1.Cells(i, "E").Value = "Resign"
        Else
            ws1.Cells(i, "E").Value = ""
        End If
    Next i
End Sub

优化版代码（字典高效版） #

适合数据量大的场景，利用字典的O(1)查找特性提升性能：

Sub CheckValuesWithDictionary()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lastRow1 As Long
    Dim lastRow2 As Long
    Dim i As Long
    Dim valueDict As Object
    
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    Set valueDict = CreateObject("Scripting.Dictionary")
    
    ' 将Sheet2 A列所有值存入字典
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    For i = 1 To lastRow2
        If Not valueDict.Exists(ws2.Cells(i, "A").Value) Then
            valueDict.Add ws2.Cells(i, "A").Value, True
        End If
    Next i
    
    ' 遍历Sheet1，检查值是否在字典中
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    For i = 1 To lastRow1
        If Not valueDict.Exists(ws1.Cells(i, "A").Value) Then
            ws1.Cells(i, "E").Value = "Resign"
        Else
            ws1.Cells(i, "E").Value = ""
        End If
    Next i
End Sub

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