Duplicate Value warning

[yukon_phil]

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"?

[yukon_phil]

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
>
>

[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

[Tom Hutchins]

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

"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

[yukon_phil]

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
>
>

[yukon_phil]

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(Target.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