Duplicate Value warning

[Guest]

I have used the following code to give me a warning of duplicated values in a
column, at least I thought it should do this. It does in fact give me the
warning in the column BUT it give me the warning if in the same row and this
is permitted.

How do I get the code correct so it just gives me the warning for the column
values and not the row? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
'Adjust next constant to your own needs
Const myColumn As String = "B:AF"
Dim rng As Range
Dim Found As Range

Set rng = UsedRange.Columns(myColumn)
If Intersect(Target, rng) Is Nothing _
Or Target.Value = "" _
Then Exit Sub
Set Found = rng.Find(Target.Value)
If Found.Address <> Target.Address Then
Target.Select
MsgBox ("Duplicate code")
End If
End Sub

 

[Special-K]

If youre just checking for a column of duplicates shouldnt that "B:AF
be "B:B"

 

[Guest]

If I use B:B would that not just give me the warn for duplicate values in
column "B" only, I also need to know if there are duplicate values in all
columns from B to AF but not in any row. Does this make sense?

 

[vezerid]

I think it should work if you change the line:

Set rng = UsedRange.Columns(myColumn)

with

Set rng = Target.cells(1,1).EntireColumn

Does this help?
Kostis Vezerides

 

[Guest]

How about this instead?

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.WorksheetFunction.CountIf(Columns(Target.Column),
Target.Value) > 1 Then
MsgBox "Duplicate entry"
End If
End Sub

Hope this helps,

Hutch

 

[Guest]

That did, thank you very much.

As I go through the workings of these worksheets I am coming up with more
and more issues I am unable to resolve. These discussions groups are such a
valuable resource, I think you will be seeing more questions from me, hope I
don't overdue it in people eyes, I am still learning.

Thanks again

 

[Guest]

Thanks for your input, but the code I received from Kostis worked perfectly.