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:
- runtime error ‘9’: subscript out of range error in VBA programming
- On error resume next, on error goto 0, err usage
- [Solved] Excel solver: Error Log 3_Decimal result after non negative constraint
- How to Solve Excel Error Log 1: Data format error
- How to Solve Excel Error Log 2: Sloving method
- [Solved] Excel plug in installation failed: unable to resolve the value of property ‘type’
- How to Solve Excel Error Log 0: Install Error
- [error record] Android application release package error handling (turn off syntax check log processing release configuration)
- Failed to load file or assembly “Microsoft.Office.Interop.Excel” Solution
- Tencent cloud container easyexcel export excel error NoClassDefFoundError: could not initialize class sun.awt.x11fontmanager
- POI Export Excel Error: HTTP Status 500 – Request processing failed; nested exception is java.lang.NullPointerException
- MFC:: error C2065: “IDD_DIALOG1”: undeclared identifier Sending and handling custom messages in MFC threads
- SQL Server Error: Arithmetic overflow error converting expression to data type int.
- “//./root/CIMV2” because of error 0x80041003. Events cannot be delivered through this filter until t…
- [Solved] error C2041: illegal digit ‘9‘ for base ‘8‘ | error C2059: syntax error: ‘bad suffix on number‘
- [Vue warn]: Error in render: TypeError: Cannot read property ‘xxxx’of undefined
- [Solved] Tencent cloud SMS service error: FailedOperation.TemplateIncorrectOrUnapproved
- [Solved] ASP Website Error: An error occurred on the server when processing the URL.
- [USF-XSim-62] ‘elaborate‘ step failed with errors.[Vivado 12-4473] Detected error while running sim