Macro for checking duplicate entries

[Ken]

Hey, Group!
Another post by a macro noob (trying hard to learn something)....I
have a spreadsheet that has a column for entering a unique ID number
(6 digits) Column E, and I need to know that I have entered a
previously entered ID, with a warning, and "Do you wish to
continue?"....with the warning telling me the line # of the previously
entered ID....as I have it right now I have a just a formula that will
flag the new cell and turn it a different color, with no reference to
the previously entered ID, which is ok, but lacking....I really need a
macro, and I have no idea where to start, although I can maneuver
around the VBA somewhat....I'm really struggling on this one...does
anyone have something similar that I might be able to use? Any help
will be greatly appreciated.
Thanks in advance!!!
Ken

 

[papou]

Hello Ken
Right click on the worksheet tab, select view code and paste the code below.

HTH
Cordially
Pascal

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Or Target.Cells.Count > 1 Then Exit Sub
r = Target.Row
On Error Resume Next
If Application.CountA(Range(Cells(1, 5), Cells(Target.Row - 1, 5)),
Target.Value) <> 0 Then
On Error GoTo 0
On Error Resume Next
If MsgBox("This value has been found in row " & _
Application.Match(Target.Value, Range(Cells(1, 5), Cells(Target.Row - 1,
5)), 0) _
& vbLf & "do you wish to continue?", vbQuestion + vbYesNo, "ID found") =
vbYes Then
On Error GoTo 0
Exit Sub
Else
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub

 

[Guest]

ken,

Try this. Right click the sheet tab, view code and paste this in:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
If Not Intersect(Target, Range("E2:E1000")) Is Nothing Then ' for a range
If IsNumeric(Target) Then
lastrow = Cells(Cells.Rows.Count, "e").End(xlUp).Row
Set myRange = Range("E2:E" & lastrow - 1)
For Each c In myRange
If c.Value = Target.Value Then
Address = c.Address
MsgBox "Already entered in " & Address
Exit Sub
End If
Next
End If
End If
End Sub

Mike

 

[Ken]

ken,

Try this. Right click the sheet tab, view code and paste this in:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
If Not Intersect(Target, Range("E2:E1000")) Is Nothing Then ' for a range
If IsNumeric(Target) Then
lastrow = Cells(Cells.Rows.Count, "e").End(xlUp).Row
Set myRange = Range("E2:E" & lastrow - 1)
For Each c In myRange
If c.Value = Target.Value Then
Address = c.Address
MsgBox "Already entered in " & Address
Exit Sub
End If
Next
End If
End If
End Sub

Mike





- Show quoted text -

Hey Guys,
Thank you both, Mike and Pascal, for the codes.....I have changed
the column to Column H, and have tried both codes (just pasted the
code into the blank VBA window, right??), I changed in Mike's to "H"
instead of "E", and in Pascal's I changed the "5" to "8", would that
be correct??...anyway I tried them both one at a time, but can't get
either to work...you guys will have to walk me through the steps
because there's something I'm leaving out....if the code is pasted
into the window, then all I would have to do is go back to excel, type
in a duplicate number, and the code should run, or am I completely
missing the boat??? I think that the answer might be that my worksheet
is read only right now because someone has it open on their
desktop...could that be the reason???? Again guys, I'm just starting
to understand the basics, so bear with me, please....Thanks Again for
your help!!!
Ken

 

[Ken]

Hey Guys,
Thank you both, Mike and Pascal, for the codes.....I have changed
the column to Column H, and have tried both codes (just pasted the
code into the blank VBA window, right??), I changed in Mike's to "H"
instead of "E", and in Pascal's I changed the "5" to "8", would that
be correct??...anyway I tried them both one at a time, but can't get
either to work...you guys will have to walk me through the steps
because there's something I'm leaving out....if the code is pasted
into the window, then all I would have to do is go back to excel, type
in a duplicate number, and the code should run, or am I completely
missing the boat??? I think that the answer might be that my worksheet
is read only right now because someone has it open on their
desktop...could that be the reason???? Again guys, I'm just starting
to understand the basics, so bear with me, please....Thanks Again for
your help!!!
Ken- Hide quoted text -

- Show quoted text -

It's me again! I just changed to the actual spreadsheet where I could
read/write, and Mike's code works perfectly!!! I'm not certain what I
have to change on Pascal's, but I sure would like to try it.....Ken

 

[Ken]

It's me again! I just changed to the actual spreadsheet where I could
read/write, and Mike's code works perfectly!!! I'm not certain what I
have to change on Pascal's, but I sure would like to try it.....Ken- Hide quoted text -

- Show quoted text -

Pascal, I got your code to working on my workbook! You guys are
absolutely the best, I can't thank you enough....
Ken

 

[papou]

My pleasure Ken and thanks for the feedback.

Cordially
Pascal