Using Instr to find duplicate strings in a range. Much pain.

[Sc0ut]

Hello everyone,

I'm really struggling with this one. There must be a simple way to get this function to work. I have been trying for hours but I'm not having any joy.

I simply want to stop two of the same names being entered in a range.

Here is what I have so far.

Public Function CompareNames(Rng1 As Range)

CompareNames = Rng1

Dim Cell As Range
Dim TempString As String

For Each Cell In Rng1
    If Cell.Value = "" Then
        GoTo ParseString
    End If
    TempString = TempString & Cell.Value & ";" & " "
Next Cell

ParseString:
Dim Sname As Range

For Each Sname In Rng1
    If Sname.Value = "" Then
        Exit Function
    End If

    If InStr(1, TempString, Sname, vbTextCompare) >= 0 Then
'This is the code that needs some work. I'm not sure how I can get Instr to use the Sname variable (each name in the list) 
to find duplicates in the TempString variable (all names in the list as one string separated by ";" and space).
        MsgBox "Duplication detected!"
    End If

Next Sname

End Function

I still can't get find a way to get the function to use Instr to find a duplication. The Instr finds the name that I'm using to search the string (the Sname variable), and returns a "true" result (as it has found the match). Obviously this is a problem as I only want the Instr to give a "true" result when it finds more than one of the same name.

Any help would be greatly appreciated.