424 Object required error

[J_J]

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

 

[Don Guillett]

In which group would you like your answer?

 

[E Oveson]

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]

answered on microsoft.public.excel.misc

In which group would you like your answer?

--
Don Guillett
SalesAid Software
(e-mail address removed)
to

 

[J_J]

Thank you E Overson,
Your suggestion cures the problem...
Regards
J_J