PDA

View Full Version : Error Run-time error '424'



5element
07-18-2017, 10:44 AM
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
07-18-2017, 12:39 PM
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
07-18-2017, 12:39 PM
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
07-19-2017, 07:26 AM
Some times is more row for example 1600 but it set 200 row becouse Because the macro lasted a very long time.

5element
07-19-2017, 11:37 AM
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.