Hi,

I have found this code in this forum, which checks to see if a value has
been duplicated, how do I change to look at a row instead of a column?

I have tried changing the myColumn to myRow As String="4"
and the other reference referring to Column as row, but it does not work.

Any ideas?

Thanks
Winnie

Private Sub Worksheet_Change(ByVal Target As Range)
'Adjust next constant to your own needs
Const myColumn As String = "B"
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

Hi,

Doing what you describe should have worked, try this

Private Sub Worksheet_Change(ByVal Target As Range)
'Adjust next constant to your own needs
If Target.Cells.Count 1 Then Exit Sub
Const myRow As String = "4"
Dim rng As Range
Dim Found As Range
Set rng = UsedRange.Rows(myRow)
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


Mike

"winnie123" wrote:

Hi,

I have found this code in this forum, which checks to see if a value has
been duplicated, how do I change to look at a row instead of a column?

I have tried changing the myColumn to myRow As String="4"
and the other reference referring to Column as row, but it does not work.

Any ideas?

Thanks
Winnie

Private Sub Worksheet_Change(ByVal Target As Range)
'Adjust next constant to your own needs
Const myColumn As String = "B"
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

Hi Mike,

Thank you for comming back to me so quickly.

maybe I am not understanding the following bit of code

Const myRow As String = "4"

I entered a 4 there as it was row 4 I wanted to check for duplicates, i am
assuming that this is not correct.

What would I put there if I wanted to check the active row?

"Mike H" wrote:

Hi,

Doing what you describe should have worked, try this

Private Sub Worksheet_Change(ByVal Target As Range)
'Adjust next constant to your own needs
If Target.Cells.Count 1 Then Exit Sub
Const myRow As String = "4"
Dim rng As Range
Dim Found As Range
Set rng = UsedRange.Rows(myRow)
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


Mike

"winnie123" wrote:

Hi,

I have found this code in this forum, which checks to see if a value has
been duplicated, how do I change to look at a row instead of a column?

I have tried changing the myColumn to myRow As String="4"
and the other reference referring to Column as row, but it does not work.

Any ideas?

Thanks
Winnie

Private Sub Worksheet_Change(ByVal Target As Range)
'Adjust next constant to your own needs
Const myColumn As String = "B"
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