Check for duplicates in column except target cell 1

[Etrigan]

Hi,

I have a list that is being populated continuously.

I need to compare the target cell (wich will be in column D) with the rest cells in column D and check for duplicates but NOT include the target cell in the search.

With the following code I get the result I want when there is one or more duplicates, but if the value is uniqe it "checks itself".

Private Sub Worksheet_Change(ByVal Target As Range)

With Worksheets("Partier").Columns("D")
    Set c = .Find(Target.Value, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = Target.Address
        Do
            MsgBox (c.Offset(columnoffset:=-3).Value & " " & c.Offset(columnoffset:=-1).Value)
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
End With
    
End Sub

I need to exclude the target cell in the search but I'm a newbie when it comes to VBA so I'm stuck, so please help!


Thank's

Glenn

 

[Fenrirshowl]

I think the following might be what you need (only a minor change to your code):

Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Partier").Columns("D")
    Set c = .Find(Target.Value, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = Target.Address
        Do While c.Address <> firstaddress
            If c.Address <> firstaddress Then
            MsgBox (c.Offset(columnoffset:=-3).Value & " " & c.Offset(columnoffset:=-1).Value)
            Set c = .FindNext(c)
            End If
        Loop
    End If
End With
End Sub

 

[Etrigan]

Thank you so much Fenrirshowl, it works like a charm!

A star is coming your way.

/Glenn

 

[Fenrirshowl]

Thanks Glenn

You did all the hard work though!

 

[SkipVought]

Also take a look at Data/Validation in order to PREVENT the wron data from being entered in the first place.

Skip,

_[red][/red]