What is wrong with my code??

[Guest]

I copied and edited this code to find duplicate names in my worksheet column
B and then display a msgbox to tell the user so, however it is not working at
all. Please advise

Sub FindDuplicates()
Dim colNum As String
Dim rng As Range, cell As Range
Dim rng1 As Range
colNum = "B"
With ActiveSheet
Set rng = .Range(.Cells(1, colNum), .Cells(Rows.Count, colNum).End(xlUp))
End With
For Each cell In rng
If Application.CountIf(rng, cell) > 1 Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
End If
Next
If Not rng1 Is Nothing Then
MsgBox "YOU HAVE ENTERED A DUPLICATE MANUFACTURER NAME"
End If
End Sub

 

[Guest]

Works for me
Replace your msgbox with this

MsgBox rng1.Address

 

[Guest]

Nothing I can see

You could perhaps be a bit more helpful with the message box by giving the
address of the duolicate

 

[Gary Keramidas]

does this do what you want? i see it was exiting only after finding the 1st
match.

Sub FindDuplicates()
Dim colNum As String
Dim rng As Range, cell As Range
Dim rng1 As Range
colNum = "B"
With ActiveSheet
Set rng = .Range(.Cells(1, colNum), .Cells(Rows.Count, _
colNum).End(xlUp))
End With
For Each cell In rng
If Application.CountIf(rng, cell) > 1 Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
MsgBox "YOU HAVE ENTERED A DUPLICATE MANUFACTURER NAME"
_
& " (" & cell.Value & ")"
End If
End If
Next
End Sub

 

[Guest]

The code work fine in excel 2003. make sure the worksheet is the active
worksheet before you run the code and make sure tthe code is in a module VBA
sheet. Close all workbooks except the active workbook


Also check that your security level to make sure it is not set to high or
very high.