Excel VBA数组匹配异常：添加不匹配项后后续工作表处理出错

嘿，这个问题我之前处理Excel VBA批量对比时也踩过坑！核心问题大概率是你第一次把不匹配项添加到「Sammanställning」的A列后，基准数组varr没有同步更新——后续处理其他工作表时，你还是在用最初读取的旧数组去对比，自然会出现异常（比如重复添加、漏判或者逻辑混乱）。

问题根源拆解 #

• 你一开始只在代码开头读取了一次「Sammanställning」A列的内容到varr数组，但第一次添加新数据后，A列的内容已经发生变化，后续循环里的varr还是旧数据，导致后续工作表的对比基准完全不对。
• 另外，用数组嵌套循环对比的效率很低，数据量大的时候还容易出错，建议用Collection或者Dictionary来存储基准值，查找速度更快，还能实时维护最新的基准数据。

修正后的代码示例 #

Sub FindAndAddMismatches()
    Dim wsSummary As Worksheet
    Dim ws As Worksheet
    Dim baseCollection As Collection
    Dim lastRow As Long
    Dim currentArr() As Variant
    Dim i As Long
    Dim cellValue As Variant
    
    ' 初始化汇总工作表和基准集合
    Set wsSummary = ThisWorkbook.Worksheets("Sammanställning")
    Set baseCollection = New Collection
    
    ' 先把汇总表A列的初始数据存入集合（Key用于去重）
    On Error Resume Next ' 忽略重复值的添加错误
    lastRow = wsSummary.Cells(wsSummary.Rows.Count, "A").End(xlUp).Row
    For Each cellValue In wsSummary.Range("A1:A" & lastRow).Value
        If cellValue <> "" Then
            baseCollection.Add cellValue, Key:=CStr(cellValue)
        End If
    Next cellValue
    On Error GoTo 0
    
    ' 遍历所有非汇总工作表
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsSummary.Name Then
            ' 读取当前工作表K列的数据到数组
            lastRow = ws.Cells(ws.Rows.Count, "K").End(xlUp).Row
            currentArr = ws.Range("K1:K" & lastRow).Value
            
            ' 逐个检查K列数据是否在基准集合中
            For i = 1 To UBound(currentArr)
                cellValue = currentArr(i, 1)
                If cellValue <> "" Then
                    On Error Resume Next
                    ' 尝试添加到集合，Key重复会报错，说明已存在
                    baseCollection.Add cellValue, Key:=CStr(cellValue)
                    If Err.Number = 0 Then
                        ' 没有报错，说明是新的不匹配项，添加到汇总表末尾
                        wsSummary.Cells(wsSummary.Rows.Count, "A").End(xlUp).Offset(1, 0).Value = cellValue
                    End If
                    On Error GoTo 0
                End If
            Next i
        End If
    Next ws
    
    MsgBox "所有不匹配项已成功添加到汇总表！"
End Sub

关键改进说明 #

• 用Collection实时维护基准：每次找到新的不匹配项，立刻同步添加到集合和汇总表中，后续工作表的对比基准始终是最新的，不会再出现旧数组导致的异常。
• Key去重机制：利用Collection的Key属性自动判断值是否已存在，避免了数组循环对比的低效和错误。
• 跳过空值处理：避免把空单元格误判为不匹配项，保证数据准确性。

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