|
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 |
424 Object required error
|
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com