CODEWITHSUNDEEP
excelvbaif-statementuserform
Kaz-99-
Kaz-99-

Reputation: 3

Excel Userform IF AND statement

I am trying to make an IF/AND statement in a userform.

I want to add the same Last Name and different First Name to a dynamic list.

My code is catching this as a duplicate value. I am able to add a fully unique Frist and Last Name.

In my code I pull the first name from TextBox2/Num2 and the last name from TextBox3/Num3.

Ex:
Adding John Doe to the list that already has Jane Doe will catch as a duplicate for the Last Name.

Current State of my worksheet
enter image description here

Dim Num1 As Long
Dim Num2 As Long
Dim Num3 As Long

On Error Resume Next
Num1 = Application.WorksheetFunction.Match(Me.TextBox1.Value, ws.Range("A2:A500000"), 0)
Num2 = Application.WorksheetFunction.Match(Me.TextBox2.Value, ws.Range("B2:B500000"), 0)
Num3 = Application.WorksheetFunction.Match(Me.TextBox3.Value, ws.Range("C2:C500000"), 0)
On Error GoTo 0

If Num1 > 0 Then
    MsgBox "Error! Duplicate EID detected", , "Duplicate Detected"
    Exit Sub
End If

If Num2 > 0 And Num3 > 0 Then
    MsgBox "Error! Duplicate NAME detected", , "Duplicate Detected"
    Exit Sub
End If

Upvotes: 0

Views: 185

Answers (1)

BigBen
BigBen

Reputation: 50008

Use WorksheetFunction.CountIfs instead of Match and check if the result is greater than 0. That will search for the combination of the first and last name on the same row.

Dim firstName As String
firstName = Me.TextBox2.Value

Dim lastName As String
lastName = Me.TextBox3.Value

If WorksheetFunction.CountIfs(ws.Range("B:B"), firstName, ws.Range("C:C"), lastName) > 0 Then
    MsgBox "Error! Duplicate NAME detected", , "Duplicate Detected"
    Exit Sub
End If

Upvotes: 1

Related Questions