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