PDA

View Full Version : [SOLVED] Searching for duplicates in column



sheexin
07-01-2015, 09:13 PM
Hello, I have a user form that should reject user input with a message box if there is a duplicate catalogue ISBN number entry. My code is as such:



Private Sub cmdSave_Click()


Dim ColISBN As Range


If txtCataTitle.Value = "" Then
txtCataTitle.BackColor = vbRed
lblCataTitle.ForeColor = vbRed
txtCataTitle.SetFocus
Exit Sub
End If

If txtArtist.Value = "" Then
txtArtist.BackColor = vbRed
lblArtist.ForeColor = vbRed
txtArtist.SetFocus
Exit Sub
End If

If txtNum.BackColor = vbRed Then
Exit Sub
End If

If txtCataDesc.Value = "" Then
txtCataDesc.BackColor = vbRed
lblCataDesc.ForeColor = vbRed
txtCataDesc.SetFocus
Exit Sub
End If

If txtISBN.Value = "" Then
txtISBN.BackColor = vbRed
lblISBN.ForeColor = vbRed
txtISBN.SetFocus
Exit Sub
End If

'column to search ISBN numbers in for duplicates
Set ColISBN = Range("F2", Range("F2").End(xlDown)).Find(what:=txtISBN, LookIn:=xlValues, lookat:=xlWhole)
If ColISBN = True Then
MsgBox ("Duplicate catalogue exists.")
Exit Sub

Else



'first row is empty
If Range("A2") = "" Then
Range("A2").Value = 1
Range("A2").Select
ActiveCell.Offset(0, 1).Value = txtCataTitle
ActiveCell.Offset(0, 2).Value = txtArtist
ActiveCell.Offset(0, 3).Value = txtCataDesc
ActiveCell.Offset(0, 4).Value = txtNum
ActiveCell.Offset(0, 5).Value = txtISBN
Else

'first row not empty
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
ActiveCell.Offset(0, 1).Value = txtCataTitle
ActiveCell.Offset(0, 2).Value = txtArtist
ActiveCell.Offset(0, 3).Value = txtCataDesc
ActiveCell.Offset(0, 4).Value = txtNum
ActiveCell.Offset(0, 5).Value = txtISBN
End If
Unload Me
End If


End Sub


Previously, I did not add in dim ColISBN nor


'column to search ISBN numbers in for duplicates
Set ColISBN = Range("F2", Range("F2").End(xlDown)).Find(what:=txtISBN, LookIn:=xlValues, lookat:=xlWhole)
If ColISBN = True Then
MsgBox ("Duplicate catalogue exists.")
Exit Sub


and the user form was able to add in new catalogues. However, to prevent duplicates by adding the code above, I was able to successfully detect duplicates, but now whenever I want to add a new unique entry, If ColISBN = True Then is highlighted and it shows object variable or with block variable not set. Any advice? :-)

sheexin
07-01-2015, 09:45 PM
Oh and I'm running on Excel 2011 Mac :-)

p45cal
07-01-2015, 11:24 PM
Completely untested: try changing:
If ColISBN = True Then
to:
If not ColISBN is nothing then
which should work..
However if ever the list is filtered and/or you have hidden rows, a duplicate row may not be found by the line above if it is on one of the hidden rows. As long as all the cells in column F are not formulae then a change of:
xlvalues
to:
Xlformulas
will find a duplicate even if it's hidden.

sheexin
07-01-2015, 11:54 PM
Hello! Thanks for your reply, it works indeed. Would you kindly explain why this method works instead?

SamT
07-02-2015, 12:59 AM
ColISBN is a Range Object variable when set to the results of Find. The If...Then Statement needs a condition that evaluates to a Boolean.

sheexin
07-02-2015, 01:27 AM
Thanks all for your help!