Excel VBA On Error Resume Next错误处理

原始代码
Sub test()
Dim arr() As String                                  '定义动态数组
Dim n As Long
Dim c As Integer
Dim i As Integer
Dim q As Integer
Dim pvc As Variant

Sheets("Sheet1").Select
n = Application.WorksheetFunction.CountA(Range("A:A")) '确定A列数据数量
ReDim arr(1 To n) As String                          '根据A列数据重新定义数组
For i = 1 To n
    arr(i) = Cells(i, 2)                              '给数组赋值
Next
Sheets("Experian Content Spreadsheet").Select

For c = 2 To [I65535].End(xlUp).Row Step 1
    If Len(LTrim(Range("I" & c).Value)) <> 0 Then
        pvc = Split(Range("I" & c), "|")
        For i = LBound(pvc) To UBound(pvc) Step 1
            On Error Resume Next
            q = WorksheetFunction.Match(pvc(i), arr, 0)
            If q = 0 Then
                Range("I" & c).Interior.Color = RGB(255, 255, 0)
                msgbox "I" & c
            End IF
        Next i
    End If
Next c
End Sub

修改后的代码
Sub test()
Dim arr() As String                                  '定义动态数组
Dim n As Long
Dim c As Integer
Dim i As Integer
Dim q As Integer
Dim pvc As Variant

Sheets("Sheet1").Select
n = Application.WorksheetFunction.CountA(Range("A:A")) '确定A列数据数量
ReDim arr(1 To n) As String                          '根据A列数据重新定义数组
For i = 1 To n
    arr(i) = Cells(i, 2)                              '给数组赋值
Next
Sheets("Experian Content Spreadsheet").Select

On Error Resume Next
For c = 2 To [I65535].End(xlUp).Row Step 1
    If Len(LTrim(Range("I" & c).Value)) <> 0 Then
        pvc = Split(Range("I" & c), "|")
        For i = LBound(pvc) To UBound(pvc) Step 1
            q = WorksheetFunction.Match(pvc(i), arr, 0)
            If Err.Number <> 0 Then
                Range("I" & c).Interior.Color = RGB(255, 255, 0)
                MsgBox "I" & c
                Err.Number = 0
            End If
        Next i
    End If
Next c
On Error GoTo 0
End Sub

原来判断的是Q，第一次出错时，Q未被赋值，初始值就是0，之后当出错的时候，Q没有被赋值，变成保持上一个值。实际上，你判断的不应该是Q值，应该判断是否有出错值Err.Number，当Match出错时，这个值就变了，同时不予以给Q赋值。
除非是连续出错，否则上一次的Q值不可能会是0，你自己调试一下就知道啦。
第一次用I2来执行Q值，出错跳过，Q是初始化值0
第二次用I12来执行Q值，Q值为1
……
再次出错在I34时，Q值是I32执行后的值1
http://club.excelhome.net/thread-1314400-1-1.html