Error Run-time error '424'

[5element]

Hello,

I have no idea why i have still error Run-time error '424'
In this line

If x.Value = Sheets("TMP").Cells(iCtr1, 4).Value Then

Below is my All code.

Kindly please for help.

Sub INC_Button()
Dim iListCount As Variant
Dim iCtr As Variant
Dim iListCount1 As Variant
Dim iCtr1 As Variant

Set P1_Incident = ActiveWorkbook.Worksheets("P1_Incident")
Set P1_Incident_Parent = ActiveWorkbook.Worksheets("P1_Incident_Parent")
Set P2P3_Incident = ActiveWorkbook.Worksheets("P2P3_Incident")
Set P2P3_Incident_Parent = ActiveWorkbook.Worksheets("P2P3_Incident_Parent")
Set TMP = ActiveWorkbook.Worksheets("TMP")

P1_Incident.Range("A2:A200").Copy Destination:=TMP.Range("A1")
P1_Incident.Range("C2:C200").Copy Destination:=TMP.Range("B1")
P1_Incident_Parent.Range("B2:B200").Copy Destination:=TMP.Range("C1")


P2P3_Incident.Range("A2:A200").Copy Destination:=TMP.Range("D1")
P2P3_Incident.Range("C2:C200").Copy Destination:=TMP.Range("E1")
P2P3_Incident_Parent.Range("B2:B200").Copy Destination:=TMP.Range("F1")

Application.ScreenUpdating = False

iListCount = Sheets("TMP").Range("C1:C200").Rows.Count
For Each x In Sheets("TMP").Range("A1:A200")
   For iCtr = 1 To iListCount
      If x.Value = Sheets("TMP").Cells(iCtr, 1).Value Then
         Sheets("TMP").Cells(iCtr, 1).Resize(1, 2).Delete xlShiftUp
         iCtr = iCtr + 1
      End If
        Next iCtr
         Next
         
         iListCount1 = Sheets("TMP").Range("D1:F200").Rows.Count
For Each x In Sheets("TMP").Range("F1:F200")
   For iCtr1 = 1 To iListCount1
      If x.Value = Sheets("TMP").Cells(iCtr1, 4).Value Then
         Sheets("TMP").Cells(iCtr1, 4).Resize(4, 5).Delete xlShiftUp
         iCtr1 = iCtr1 + 1
      End If
        Next iCtr1
         Next

Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

[Paul_Hossler]

1. I'd expect iListCount to always = 200

iListCount = Sheets("TMP").Range("C1:C200").Rows.Count 
    For Each x In Sheets("TMP").Range("A1:A200") 
        For iCtr = 1 To iListCount 
            If x.Value = Sheets("TMP").Cells(iCtr, 1).Value Then 
                Sheets("TMP").Cells(iCtr, 1).Resize(1, 2).Delete xlShiftUp 
                iCtr = iCtr + 1 
            End If 
        Next iCtr 
    Next

2. Dim x As Range

and try

    For Each x In Sheets("TMP").Range("A1:A200").Cells

3. If you're deleting things, it's safer to start at the bottom and work your way to the top

[SamT]

First step is always place "Option Explicit" at the top of all code modules
Note that this forces you to explicitly declare all variables. It also allows the VBIDE to check for some errors in your code.

iListCount = Sheets("TMP").Range("C1:C200").Rows.Count
The Row count of a 200 row range is always 200


For iCtr = 1 To 200
If x.Value = Sheets("TMP").Cells(iCtr,. . .
iCtr = iCtr + 1
iCtr increments twice after deleting some Rows.meaning it always skips the 2 cells after the deleted cells.

Sooner or later, you will run out of iCtr1 cells before iCtr reaches 200.

Instead of a fixed ListCount = 200, in both loops use
For iCtr = 1 To Sheets("TMP").Cells(Rows.Count, "C").End(xlUp).Row Or Cells(Rows.Count, "D") as appropriate

In the first loop, you are resizing the deletion range to include column "D" of the same Row.

In the next loop, you are resizing the deletion selection, starting in column "D" to 4 rows deep and 5 columns wide, but according to your code, there is only column "F" with anything in it.

Finally: Just what is a Run-time error '424'? I've only memorized the first 399 error codes. I know I should try harder, but there are thousands of error codes in each MS Application

[5element]

Some times is more row for example 1600 but it set 200 row becouse Because the macro lasted a very long time.

[5element]

Finally: Just what is a Run-time error '424'? I've only memorized the first 399 error codes. I know I should try harder, but there are thousands of error codes in each MS Application

Information is Run time error 424 object required.