PDA

View Full Version : Solved: Adding to a list - Preventing Duplicates



Hoopsah
12-11-2007, 07:15 AM
Hi,

I looked at a couple of the other threads on here, but to be honest :dunno

I have a list, that the user inputs to, and I want excel to check that the name doesn't get input if it already exists.

Can someone have a look at the coding below and let me know where to insert a check and how to insert a check.

Heres my code:

Sub Add_New_Designer_Data()
'
' Add_New_Designer_Data Macro
' Macro recorded 02/11/2007 by Gerry McNally
'
'
Application.ScreenUpdating = False
Sheets("Designer_ID").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Add New Designer").Select
Range("E16").Select
Selection.Copy
Sheets("Designer_ID").Select
Range("A216").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Add New Designer").Select
ActiveCell.FormulaR1C1 = ""
Application.ScreenUpdating = True
Range("E16").Select
MsgBox ("New Designer Added")
End Sub

Bob Phillips
12-11-2007, 07:23 AM
Where is the list?

Where is the code that is the user adding to?

Hoopsah
12-11-2007, 07:47 AM
Sorry, I thought the code would suffice.

Please find Input sheet and list attached....

Hoopsah
12-11-2007, 08:29 AM
?????

Zack Barresse
12-11-2007, 12:28 PM
Duplicate: http://www.theofficeexperts.com/forum/showthread.php?t=8788

Please do not cross-post. http://www.excelguru.ca/node/7

mdmackillop
12-11-2007, 12:34 PM
Sub Add_New_Designer_Data()
Dim Test As Range
Dim NewName As String
Dim MyList As Range
Dim i As Long
NewName = Range("E16")
Set MyList = Sheets("Designer_ID").Columns(1)
Set Test = MyList.Find(NewName, lookat:=xlWhole)
If Test Is Nothing Then
i = Application.Match(NewName, MyList, 1)
With Cells(i + 1, 1)
.Insert Shift:=xlDown
.Offset(-1).Value = NewName
End With
MsgBox ("New Designer Added")
End If
End Sub

Zack Barresse
12-11-2007, 12:41 PM
On the link I posted, prior to closing the thread, I forgot to add a sort routine to it. MDs solution does have that capability, although I would not use the Insert method. Same results should be achieved though. :)

Btw, if you cross-post, post a link.

i hate cross posters|i hate cross posters|i hate cross posters|i hate cross posters|i hate cross posters...