Preventing duplicates in Excel VBA

[Thadius]

I am working with a user form that I need to check for duplicates and then return a message to the user if an item already exsists.

Any help would be greatly appreciated.

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long

Dim ws As Worksheet
Set ws = Worksheets("Items")

'find first empty row in database
''lRow = ws.Cells(Rows.Count, 1) _
'' .End(xlUp).Offset(1, 0).Row

'revised code to avoid problems with
'Excel lists and tables in newer versions
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'Verifying Customer
If Trim(Me.cboCustomer.Value) = "Select" Or Me.cboCustomer.Value = "" Then
Me.cboCustomer.SetFocus
MsgBox "Need a customer...duh!"
Exit Sub
End If

'Verifying Item
If Trim(Me.txtItem.Value) = "" Then
Me.txtItem.SetFocus
MsgBox "What good is a new item without an item number?"
Exit Sub
End If

'Verifying Unit of Measure
If Trim(Me.cboUoM.Value) = "Select" Or Me.cboUoM.Value = "" Then
Me.cboUoM.SetFocus
MsgBox "Kind of need to know how to count this new item..."
Exit Sub
End If

'Prevent duplicates


'copy the data to the database

With ws
.Cells(lRow, 1).Value = Me.cboCustomer.Value
.Cells(lRow, 2).Value = Me.txtItem.Value
.Cells(lRow, 3).Value = Me.txtdesc.Value
.Cells(lRow, 4).Value = Me.cboUoM.Value
End With

'clear the data
Me.cboCustomer.Value = "Select"
Me.txtItem.Value = ""
Me.txtdesc.Value = ""
Me.cboUoM.Value = "Select"
Me.cboCustomer.SetFocus

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_Click()

End Sub

 

[SkipVought]

Hi,

Since this is an Excel question, you would be better served in forum68 or forum707.

Why not simply use the Data > Remove duplicates feature?



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Why did you post another thread on this subjuct?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]