工作簿索引循环的错误处理问题：Match函数匹配失效异常

尝试对多个工作表中的指定索引值求和，现有代码能运行但错误处理存在问题。当Match函数找不到匹配项时会返回错误，使用On Error Resume Next后脚本可完整执行，但Sheet5至Sheet8中，Match函数未正确匹配Crit1变量，导致求和结果不符合预期。

原代码 #

For i = 3 To ThisWorkbook.Worksheets.Count
    Set ws = ThisWorkbook.Worksheets(i)
    Debug.Print "Work Plz " & ws.Name
    On Error Resume Next
    Cat = WorksheetFunction.Match(Crit1, ws.Range("B4:B34"), 0)
    Dat = WorksheetFunction.Match(Crit2, ws.Range("C2:N2"), 0)
    Val = WorksheetFunction.Index(ws.Range("C4:N34"), Cat, Dat)
    Indx = Indx + Val
    Debug.Print "The Match and Index " & Cat & ", " & Dat & ", " & Indx
Next i

预期输出 #

2024
Work Plz Sheet3
The Match and Index 14, 3, 0
Work Plz Sheet4
The Match and Index 14, 3, 0
Work Plz Sheet5
The Match and Index 14, 3, 0
Work Plz Sheet6
The Match and Index 14, 3, 0
Work Plz Sheet7
The Match and Index 14, 3, 0
Work Plz Sheet8
The Match and Index 14, 3, 0

实际输出 #

2024
Work Plz Sheet3
The Match and Index 14, 3, 0
Work Plz Sheet4
The Match and Index 14, 3, 0
Work Plz Sheet5
The Match and Index 14, 3, 0
Work Plz Sheet6
The Match and Index 14, 3, 95.74
Work Plz Sheet7
The Match and Index 14, 3, 95.74
Work Plz Sheet8
The Match and Index 14, 3, 95.74

问题原因 #

On Error Resume Next会忽略所有错误，当Sheet5至Sheet8中Match(Crit1, ws.Range("B4:B34"), 0)匹配失败时，Cat会保留上一次循环的有效值（14）。如果此时Dat匹配成功，Index会取到该工作表中第14行第3列的单元格值，进而错误累加到Indx中，导致结果不符合预期。

修复方案 #

改用Application.Match替代WorksheetFunction.Match，前者匹配失败时返回错误值，可通过IsError判断；同时每次循环重置变量，避免旧值干扰。

修改后代码 #

For i = 3 To ThisWorkbook.Worksheets.Count
    Set ws = ThisWorkbook.Worksheets(i)
    Debug.Print "Work Plz " & ws.Name
    
    ' 重置变量，避免上一次循环的旧值影响
    Cat = 0
    Dat = 0
    Val = 0
    
    ' 检查Crit1匹配结果，成功则赋值
    If Not IsError(Application.Match(Crit1, ws.Range("B4:B34"), 0)) Then
        Cat = Application.Match(Crit1, ws.Range("B4:B34"), 0)
    End If
    
    ' 检查Crit2匹配结果，成功则赋值
    If Not IsError(Application.Match(Crit2, ws.Range("C2:N2"), 0)) Then
        Dat = Application.Match(Crit2, ws.Range("C2:N2"), 0)
    End If
    
    ' 仅当两个匹配都成功时，才取值并累加
    If Cat > 0 And Dat > 0 Then
        Val = Application.Index(ws.Range("C4:N34"), Cat, Dat)
        Indx = Indx + Val
    End If
    
    Debug.Print "The Match and Index " & Cat & ", " & Dat & ", " & Indx
Next i

代码说明 #

• 每次循环重置Cat、Dat、Val为0，确保未匹配成功时变量不会保留旧值。
• 通过IsError判断匹配结果，仅在成功时赋值，避免错误触发。
• 只有两个匹配都有效时才执行取值和累加操作，彻底避免错误数据进入求和逻辑。

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