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
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