Specify Row

[winnie123]

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

 

[Mike H]

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]

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?

 

[winnie123]

Sorry,

I will be more specific.

I'm using Excel 2003

What I want to do is check for duplicates on the same row.

C4 is data validation and when a value is selected it goes to D4, C4 then
clears contents, then I can select again from the list it enters the value in
E4 and so on until Q4.

The next row would be row 7, then row 10 etc until row 34

What I need to do is prevent duplicate selection. So if duplicated entry is
found then message box to appear, "you have already made that selection do
you wish to continue?" then have Yes/No so that it either exists the sub or
clear the contents of that cell.

I have tried data validation on the range D4:Q4 as per the tips on
Contextures web Site, but that did not work. So was looking at code to see if
i could achieve this by code.

Hope this is enough info to go on.

Thanks

Winnie

 

[Don Guillett]

without seeing, I'm still confused.
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[winnie123]

Thank Don,

I have sent the file.

best regards

Winnie

 

[Patrick Molloy]

so
in C in various rows, you have a validated list. when the user selects an
item, you want that item pasted into the next available cell to the right
and the item removed from the list

in this demo file
http://cid-b8e56c9a5f311cb7.skydriv...c/Excel Files/RowValidation/RowValidation.xls

i have several rows' where Cnn (nn=4,7,10...) where in C there's a
validation list. clicking an item copies it to the next clear cell to the
right and removes it from the validation list

is this what you're looking for?

 

[winnie123]

Hi Patrick,
Thanks for your reply.

I looked at your file and that is what I have now, all be it not the same
macro.

However the bit I am struggling with is that you can select the same value
more than once allowing duplicate entries for the same row, which will
sometimes be ok.

So at the point the selection is made and the value goes to the next
available cell to the right I need a check on that row which will highlight
that the selection is already there, and the user can them select to delete
the newly added record or keep it.

The data entry rows are 4,7,10,13,16, and so on until row 34

The 2 rows inbetween rows 5 - 6 etc have calculations in them

Any help is very much appreciated.

The current macro I have is

Option Explicit



Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler

Dim rngDV As Range
Dim iCol As Integer

Sheets("Data Entry").Unprotect Password:="builder"

If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
If Target.Column = 3 Then
If Target.Value = "" Then GoTo exitHandler
If Target.Validation.Value = True Then
iCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
Cells(Target.Row, iCol).Value = Target.Value
Else
MsgBox "Invalid entry"
Target.Activate
End If
End If
End If

If Not Application.Intersect(Target,
Range("C4,C7,C10,C13,C16,C19,C22,C25,C28,C31,C34")) _
Is Nothing Then
Selection.ClearContents
End If

Sheets("Data Entry").Protect Password:="builder"
exitHandler:
Application.EnableEvents = True

End Sub

 

[Patrick Molloy]

with the code i sent, its not possible to re-select an item from the
validation list. when ian item is first selected it is copied to a free cell
and then deleted from the list.

 

[Don Guillett]

Private Sub Worksheet_Change(ByVal Target As Range) 'SAS
Dim r As Long
Dim lc As Long
Dim ans As String
Dim rngDV As Range
If Target.Count > 1 Or Target.Column <> 3 Then Exit Sub

'Me.Unprotect Password:="builder"
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
If Intersect(Target, rngDV) Is Nothing Then
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
Exit Sub
End If

r = Target.Row
lc = Cells(r, Columns.Count).End(xlToLeft).Column + 1
Application.EnableEvents = False
Cells(r, lc) = Target
Application.EnableEvents = True
If Application.CountIf(Range(Cells(r, "d"), Cells(r, "Q")), Target) > 1 Then
ans = MsgBox("Duplicated, Continue?", vbYesNo)
If ans = vbNo Then
Cells(r, lc) = ""
End If
Target = ""
End If
'Me.Protect Password:="builder"
End Sub