Excel VBA on error resume next error handling

Original Code
Sub test()
Dim arr() As String                                  'Define dynamic arrays
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")) 'Determine the amount of data in column A
ReDim arr(1 To n) As String 'Redefine array based on column A data.
For i = 1 To n
    arr(i) = Cells(i, 2) 'Assign value to array
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                                  'Define dynamic arrays
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")) 'Determine the amount of data in column A
ReDim arr(1 To n) As String 'Redefine array based on column A data.
For i = 1 To n
    arr(i) = Cells(i, 2) 'Assign value to array
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

The first time an error occurs, Q is not assigned a value, and the initial value is 0. Later, when an error occurs, Q is not assigned a value, and the previous value is kept. In fact, you should not be judging Q, but whether there is an error value Err.
Number, which is changed when Match makes an error, and is not assigned to Q. Unless there are consecutive errors, the last Q value cannot be 0.
The first time you use I2 to execute Q, the error is skipped and Q is the initial value of 0.
The second time I12 is used to perform Q with a value of 1.
......
Error again at I34, where the Q value is the value after I32 execution1

 

Read More: