PDA

View Full Version : [SOLVED] How to Compare a textbox entry to a column and find duplicates?



kann
04-12-2016, 06:25 PM
I Have a column that contains alphanumeric and a special character '-' like
Kat-1
kat-2
Amp-3
Dsa-4

I need to compare my textbox entry from an user form to this column and find duplicates. In case of finding it, then pop-up a message for duplicate entry. I tried the following code but it does not seem to be detecting a duplicate. I need a duplicate message only when an exact match like 'kat-1' is entered by the user. Other values like kat-2, Amp-1, etc... should be allowed by the program.


Private Sub txt_BPName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim myrange As Range
Dim match As Boolean
Dim val
Set myrange = Worksheets("Sheet1").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).row)
match = WorksheetFunction.CountIf(myrange, val) > 0
If match Then
MsgBox ("Duplicate")
Cancel = True
End If
End Sub

Can anyone please take a look at it and provide a possible solution.

Thanks in advance,
kann

jolivanes
04-12-2016, 07:03 PM
Where is your TextBox1, or 2 or 3, value in your code?

kann
04-12-2016, 07:23 PM
Text box name is txt_BPName (Base Product) present on the user form.

Sheet 1 of the attached macro has A1,B1,C1 & E1 opening the same user form upon clicking it. 15908

jolivanes
04-12-2016, 07:32 PM
I don't know if I understand you right, but this does what you ask for.

Private Sub CommandButton1_Click()
Dim c As Range
For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If c.Value = TextBox1 Then MsgBox "Cell " & c.Address & " is a duplicate."
Next c
End Sub

kann
04-12-2016, 07:50 PM
For inputs like A12, Kat-2 the program displays a duplicate message. These are values not present in column A and hence needs to be allowed. Duplicate message is required for "exact " duplicate entries from the user.

jolivanes
04-12-2016, 09:56 PM
Attach a workbook with all your possibilities.
And play with the attached

kann
04-13-2016, 06:02 AM
Can we have the same functionality on just a textbox without the commandbutton (check for Dups). I am trying to use it in the exit event of the textbox like below

Private Sub txt_BPName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).row)
If c.Value = txt_BPName Then MsgBox "Cell " & c.Address & " is a duplicate."
Next c
Cancel = True
End Sub

The Problem is the program is seeing my immediate entry as a duplicate. For example, When I type in 'Kat-1' it gets an entry in "A23" and later gets compared to itself in the for loop. How to avoid this.

kann
04-13-2016, 07:12 AM
I have modified your spreadsheet to suit exactly to my question. Please take a look at it.

When user enters 'Amp-3' for example, he should not see the value in cell A13 as a duplicate.

jolivanes
04-13-2016, 08:15 AM
Cell A13 is blank!!!!

kann
04-13-2016, 08:37 AM
Cell A 13 is the place where user input gets stored. Place your cursor at A13, then click button 1 and type Kat-3 as your input for Base Product. You should see that Kat-3 is considered as a duplicate, while it should not be. The program is comparing an immediate entry by the user( 'kat-3') to itself in the for loop. I am asking for a way to avoid this.

jolivanes
04-13-2016, 10:47 AM
I am very sorry but I can't follow your logic.
I hope someone else will be able to understand it and help you.

kann
04-13-2016, 06:42 PM
No worries. I used the code like below to solve my problem.

Private Sub txt_BPName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Row As Integer
Row = ActiveCell.Row
Dim c As Range
Dim found As Boolean


found = False
For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If c.Value = txt_BPName Then MsgBox "Cell " & c.Address & " is a duplicate."
If c.Value = txt_BPName Then found = True
If found = True Then Exit For

Next
If found = False Then
Cells(Row, 1).Value = txt_BPName.Text
Add_Inv.Hide
End If

Cancel = True

End Sub


Your initial code helped to get here. So, thanks for your help.

Regards,
Kann