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

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


--
Special-K


------------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=562514

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?

"Special-K" wrote:


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


--
Special-K


------------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=562514

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

How about this instead?

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

Hope this helps,

Hutch

"yukon_phil" wrote:

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

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

"vezerid" wrote:

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

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

"Tom Hutchins" wrote:

How about this instead?

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

Hope this helps,

Hutch

"yukon_phil" wrote:

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