Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for checking duplicate entries
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for checking duplicate entries
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 "Ken" a écrit dans le message de news: ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for checking duplicate entries
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" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for checking duplicate entries
On Sep 14, 5:28 am, Mike H wrote:
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" wrote: 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- Hide quoted text - - 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for checking duplicate entries
On Sep 14, 9:28 am, Ken wrote:
On Sep 14, 5:28 am, Mike H wrote: 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" wrote: 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- Hide quoted text - - 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- 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for checking duplicate entries
On Sep 14, 10:46 am, Ken wrote:
On Sep 14, 9:28 am, Ken wrote: On Sep 14, 5:28 am, Mike H wrote: 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" wrote: 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- Hide quoted text - - 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- 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- 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for checking duplicate entries
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking entries against a column of data | Excel Discussion (Misc queries) | |||
Checking for double entries | Excel Discussion (Misc queries) | |||
Checking for duplicate entries | Excel Discussion (Misc queries) | |||
Checking for duplicate entries over multiple worksheets | Excel Discussion (Misc queries) | |||
Checking for duplicate items in list | Excel Worksheet Functions |