stop duplicates

[paulwol]

Im looking to control what gets keyed into cell, based on if its already in the column and another criteria.


ie
column a column b
rte complete
11001 no

if I key in 11001 in the next cell down, I want excel to search updwards and let me know if its there if it is and column b it says no then msg box to say its already logged and outstanding, if column b says yes, then allow the user to key the data in.
hope this makes sense!

[SamT]

Right Click on the Sheet Tab and choose "View Code."

Paste This Code in the Code Pane
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub
Dim Found As Range

Set Found = Range("A:A").Find(What:=Target, After:=Target, SearchDirection:=xlPrevious)
If Found.Row >= Target.Row Then Exit Sub

If Not LCase(Found.Offset(0, 1)) = "no" Then Exit Sub

Target.Value = vbNullString
MsgBox "Already Logged And Outstanding."

End Sub

[paulwol]

thanks for the reply.. ive done the below, but doesn't do anything.

so my data is
cell a1 has 11001
cell b1 has no
so when I key 11001 into cell a2 I want it to search column A for 11001 and not let me due to b1 saying no
thanks

[Kenneth Hobs]

If you want to use Data Validation, then in A3:
Data Validation > Allow > Custom > Formula: =COUNTIFS($A$2:A2,A3, $B$2:B2,"no")=0
Add MsgBox's as needed...

Then: copy > select range to copy to > Paste Special > Paste Special > Validation > OK.

[Kenneth Hobs]

I would use Sam's code some like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 3 Or Target.Column <> 1 Or Target.Cells.Count <> 1 Then Exit Sub

Dim Found As Range
Debug.Print Range("A2", Target.Offset(-1)).Address
Set Found = Range("A2", Target.Offset(-1)).Find _
(What:=Target.Value, After:=Target.Offset(-1), Lookat:=xlWhole, SearchDirection:=xlNext)
If Found Is Nothing Then Exit Sub
If Found.Row >= Target.Row Then Exit Sub

If Not LCase(Found.Offset(0, 1)) = "no" Then Exit Sub

Target.Value = vbNullString
Target.Select
MsgBox "Already Logged And Outstanding."
End Sub

[paulwol]

brilliant...

[paulwol]

could I reference a row or something in a cell? as each line of data has a ref number ie row roill be 00001, row 2 will be 00002 etc... can we add if it finds a duplicate and says already logged and outstanding under ref 00001 ( or what ever is in cell c )

[Kenneth Hobs]

Not sure exactly what you mean. You can attach an example by clicking Go Advanced button in bottom right of a reply, paperclip icon on toolbar, and browse to the file to upload.

I showed the address of the Found cell in column C but you can use Value or such.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range

With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
On Error GoTo EndNow

With Target
If .Row < 3 Or .Column <> 1 Or .Cells.Count <> 1 Then GoTo EndNow

Set Found = Range("A2", .Offset(-1)).Find _
(What:=.Value2, After:=.Offset(-1), Lookat:=xlWhole, SearchDirection:=xlNext)
If Found Is Nothing Then Exit Sub
If Found.Row >= .Row Then Exit Sub
If Not LCase(Found.Offset(, 1)) = "no" Then GoTo EndNow

.Value = ""
.Select
MsgBox "Already Logged And Outstanding: " & Found.Offset(, 2).Address
End With
EndNow:
With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlCalculationSemiautomatic
End With
End Sub

[SamT]

I really like your use of
Set Found = Range("A2", Target.Offset(-1)).Find(... )Much more elegant then My crude offering


I showed the address of the Found cell in column C.Uh... no

This would put it in the message box
MsgBox "Already Logged And Outstanding: " & Found.Address

Superfluous, can't happen in your more elegant code
If Found.Row >= Target.Row Then Exit Sub

[paulwol]

I have attached a workbook

thanks for this. When it finds a duplicate and references the cell its in, can it reference the value in the cell?
. How can I change the code to look in different columns?

ie

if I add 11001 into B3 I want it to say Already logged and outstanding under Ref 00002 ( which is the value in A2


also, im unsure on the latest reply from Sam.
thanks again from a total novice!

[Kenneth Hobs]

Easily done with a few changes. Sam's comments were just that, no need to worry about them.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range

With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
On Error GoTo EndNow

With Target
If .Row < 3 Or .Column <> 2 Or .Cells.Count <> 1 Then GoTo EndNow

Set Found = Range("B2", .Offset(-1)).Find _
(What:=.Value2, After:=.Offset(-1), Lookat:=xlWhole, SearchDirection:=xlNext)
If Found Is Nothing Then GoTo EndNow
If Not LCase(Found.Offset(, 1)) = "no" Then GoTo EndNow

.Value = ""
.Select
Application.EnableEvents = True
MsgBox "Already Logged And Outstanding: " & _
"Ref: " & Found.Offset(, -1).Value & " in " & Found.Offset(, -1).Address
End With


EndNow:
With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With
End Sub

[paulwol]

the above is fantastic, thank you.

so I can understand it and potential adapt it to look in different columns if need be, say

Ref in column A
Route in Column E ( this is the 11001 bit)
Complete in Column CV
how do I show this in the code?

[Kenneth Hobs]

I can change the code to let you set the column references early on.

You should understand though that there are many ways to do it. Once you know a reference cell, Offset, Row, and Column can be used as needed.

Note that in this example, the MsgBox()'s return the same result. I just played with one column reference by letter and number.

Sub ken()
Dim cRef As String, cRte As String, cCom As String
Dim r As Range, c As Range

cRef = "A"
cRte = "E"
cCom = "CV"

Set r = Range("E3")

Set c = Cells(r.Row, 1)
MsgBox c.Address & vbLf & c.Value, vbInformation, r.Address

Set c = Cells(r.Row, cRef)
MsgBox c.Address & vbLf & c.Value, vbInformation, r.Address

Set c = Range(cRef & r.Row)
MsgBox c.Address & vbLf & c.Value, vbInformation, r.Address

Set c = r.Offset(, -4)
MsgBox c.Address & vbLf & c.Value, vbInformation, r.Address
End Sub

[Kenneth Hobs]

If you don't know what a command word does, press F2 while in the Visual Basic Editor (VBE) and search for it or simply place your cursor in or next to the word and press F1.

Here is how to use what I last posted.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range
Dim cRef As String, cRte As String, cCom As String

cRef = "A"
cRte = "E"
cCom = "CV"

With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
On Error GoTo EndNow

With Target
If .Row < 3 Or .Column <> Cells(1, cRte).Column _
Or .Cells.Count <> 1 Then GoTo EndNow

Set Found = Range(cRte & 2, Range(cRte & .Offset(-1).Row)).Find _
(What:=.Value2, After:=.Offset(-1), Lookat:=xlWhole, SearchDirection:=xlNext)
If Found Is Nothing Then GoTo EndNow
If Not LCase(Range(cCom & Found.Row).Value2) = "no" Then GoTo EndNow

.Value = ""
.Select
Application.EnableEvents = True
MsgBox "Already Logged And Outstanding: " & _
"Ref: " & Range(cRef & Found.Row).Value & " in " & _
Range(cRef & Found.Row).Address
End With


EndNow:
With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With
End Sub