| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
424 Object required error
Hi,
The below code manages to find if the inputted row is a duplicate of a previous record and deletes it sucessfully but the VBA compiler gives an error message as shown on the subject of this message... Can someone correct the code? '-------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Column <= 5 Then ' 3 to 5 stand for C to E columns For i = 1 To Target.Row - 1 If (Cells(i, 3) = Cells(Target.Row, 3)) And (Cells(i, 4) = Cells(Target.Row, 4)) And (Cells(i, 5) = Cells(Target.Row, 5)) Then MsgBox "Duplicate record!" Target.EntireRow.Delete End If Next End If End Sub '-------------------- Thanks J_J |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
424 Object required error
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
424 Object required error
answered on microsoft.public.excel.misc
"Don Guillett" wrote in message ... In which group would you like your answer? -- Don Guillett SalesAid Software "J_J" wrote in message ... Hi, The below code manages to find if the inputted row is a duplicate of a previous record and deletes it sucessfully but the VBA compiler gives an error message as shown on the subject of this message... Can someone correct the code? '-------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Column <= 5 Then ' 3 to 5 stand for C to E columns For i = 1 To Target.Row - 1 If (Cells(i, 3) = Cells(Target.Row, 3)) And (Cells(i, 4) = Cells(Target.Row, 4)) And (Cells(i, 5) = Cells(Target.Row, 5)) Then MsgBox "Duplicate record!" Target.EntireRow.Delete End If Next End If End Sub '-------------------- Thanks J_J |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
424 Object required error
should disable events to avoid recursion, and also break out of loop after
finding first duplicate -Erik Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 3 And Target.Column <= 5 Then ' 3 to 5 stand for C to E For i = 1 To Target.Row - 1 If (Cells(i, 3) = Cells(Target.Row, 3)) And (Cells(i, 4) = Cells(Target.Row, 4)) And (Cells(i, 5) = Cells(Target.Row, 5)) Then MsgBox "Duplicate record!" Target.EntireRow.Delete Exit For End If Next End If Application.EnableEvents = True End Sub "J_J" wrote in message ... Hi, The below code manages to find if the inputted row is a duplicate of a previous record and deletes it sucessfully but the VBA compiler gives an error message as shown on the subject of this message... Can someone correct the code? '-------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Column <= 5 Then ' 3 to 5 stand for C to E columns For i = 1 To Target.Row - 1 If (Cells(i, 3) = Cells(Target.Row, 3)) And (Cells(i, 4) = Cells(Target.Row, 4)) And (Cells(i, 5) = Cells(Target.Row, 5)) Then MsgBox "Duplicate record!" Target.EntireRow.Delete End If Next End If End Sub '-------------------- Thanks J_J |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
424 Object required error
Thank you E Overson,
Your suggestion cures the problem... Regards J_J "E Oveson" wrote in message ... should disable events to avoid recursion, and also break out of loop after finding first duplicate -Erik Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 3 And Target.Column <= 5 Then ' 3 to 5 stand for C to E For i = 1 To Target.Row - 1 If (Cells(i, 3) = Cells(Target.Row, 3)) And (Cells(i, 4) = Cells(Target.Row, 4)) And (Cells(i, 5) = Cells(Target.Row, 5)) Then MsgBox "Duplicate record!" Target.EntireRow.Delete Exit For End If Next End If Application.EnableEvents = True End Sub "J_J" wrote in message ... Hi, The below code manages to find if the inputted row is a duplicate of a previous record and deletes it sucessfully but the VBA compiler gives an error message as shown on the subject of this message... Can someone correct the code? '-------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Column <= 5 Then ' 3 to 5 stand for C to E columns For i = 1 To Target.Row - 1 If (Cells(i, 3) = Cells(Target.Row, 3)) And (Cells(i, 4) = Cells(Target.Row, 4)) And (Cells(i, 5) = Cells(Target.Row, 5)) Then MsgBox "Duplicate record!" Target.EntireRow.Delete End If Next End If End Sub '-------------------- Thanks J_J |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Object Required Error | Excel Programming | |||
| Object required - run time error 424. | Excel Programming | |||
| runtime error ....object required | Excel Programming | |||
| Syntax Error Runtime Error '424' Object Required | Excel Programming | |||
| error 424 - Object Required | Excel Programming | |||
Hybrid Mode
