Adding a second value to search for

[rickblunt]

Greetings, I have a spreadsheet where I track whether or not visitors have been authorized to enter our plant. I am using the following code in a userform to see if the person has been approved yet or not, and I am currently just searching by the last name. However (I guess it was only a matter of time) someone showed up today that had the same last name as someone else. So I would like to tweak the code to look for the last and the first name.

On the userform the textbox for this value is "FirstNameBox" and the value is passed to column B. But I am not sure what command to use - would this be "And If"? I took a couple of runs at it with no luck, so I thought before I waste any more time I should confirm what statement I should be using to begin with. I appreciate any assistance - thanks, Rick

Sub CheckName_Click()
 Dim Found As Range
 Dim i As Long
 Dim Lastrow As Long
 
'This string looks to see if the person is in the database yet.


    If Me.LastNameBox.Value = "" Then
        MsgBox "No last name was entered, please enter the last name and try again ", , "No Name Entered"
          
    Else
    
    If Me.LastNameBox.Value = "" Then
        MsgBox "No last name was entered, please enter the last name and try again ", , "Add name and try again"
        
    Else
   
        Set Found = Sheets("Accepted Visitors").Range("A:A").Find(What:=Me.LastNameBox.Value, _
                                                       LookIn:=xlValues, _
                                                       LookAt:=xlWhole, _
                                                       SearchOrder:=xlByRows, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False)
        If Found Is Nothing Then
            MsgBox "No match for " & FirstNameBox.Value & " " & Me.LastNameBox.Value & ", a EUC clearance must be obtained.", , "No Match Found"
        Else
        
        MsgBox FirstNameBox.Value & " " & Me.LastNameBox.Value & " has obtained a EUC clearance, no further actions are necessary.", , "Match Found"
  
  Exit Sub
 
        End If
        End If
        End If
    
End Sub

 

[dmt32]

Hi,
use Range.FindNext Method in your code

see if this update to your code does what you want

Option Base 1
Sub CheckName_Click()
    Dim Found As Range
    Dim Lastrow As Long
    Dim i As Integer
    Dim Search(1 To 2) As String, FirstAddress As String, msg As String
    Dim Prompt As Variant
    Dim wsVisitors As Worksheet
    
'This string looks to see if the person is in the database yet.
    Search(1) = Me.LastNameBox.Value
    Search(2) = Me.FirstNameBox.Value
    
'intialize MsgBox Prompts
    Prompt = Array("Record Not Found", "Record Not Found EUC clearance must be obtained.", _
    "has obtained a EUC clearance, no further actions are necessary.")
    
    On Error GoTo exitsub
    Set wsVisitors = ThisWorkbook.Worksheets("Accepted Visitors")
    
    If Search(1) = "" Then
        MsgBox "No last name was entered, please enter the last name and try again ", 48, "Add name and try again"
        
    Else
'search last name
        Set Found = wsVisitors.Range("A:A").Find(What:=Search(1), _
                                                LookIn:=xlValues, LookAt:=xlWhole, _
                                                SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                MatchCase:=False)
        If Found Is Nothing Then
'no match for last name
            i = 2
        Else
            i = UBound(Prompt)
'search first name (optional)
            If Len(Search(2)) > 0 Then
                FirstAddress = Found.Address
                Do
                    If Found.Offset(, 1).Value = Search(2) Then GoTo exitsub
                    Set Found = wsVisitors.Columns(1).FindNext(Found)
                Loop While FirstAddress <> Found.Address
                i = 2
            End If
        End If
    End If
    
exitsub:
    If Err <> 0 Then
        MsgBox (Error(Err)), 48, "Error"
    Else
        MsgBox Search(1) & " " & Search(2) & Chr(10) & _
                Prompt(i), 64, IIf(i = UBound(Prompt), "Record Found", "Record Found")
    End If
End Sub

I have made inclusion of searching with firstname optional.

Hope Helpful

Dave

 

[Fluff]

Another option would be

Option Explicit
Private Dic As Object

Private Sub UserForm_Initialize()

   Dim v1 As String, v2 As String
   Dim Cl As Range

   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = vbTextCompare

   With Sheets("Accepted Visitors")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         v1 = Cl.Value: v2 = LCase(Cl.Offset(, 1).Value)
         If Not Dic.exists(v1) Then
            Dic.Add v1, CreateObject("scripting.dictionary")
            Dic(v1).Add v2, CreateObject("scripting.dictionary")
         ElseIf Not Dic(v1).exists(v2) Then
            Dic(v1).Add v2, CreateObject("scripting.dictionary")
         End If
      Next Cl
   End With
End Sub

Sub CheckName_Click()
'This string looks to see if the person is in the database yet.
If Len(LastNameBox) = 0 Or Len(FirstNameBox) = 0 Then
   MsgBox "You must enter both first & last names"
   Exit Sub
End If
If Dic.exists(LastNameBox.Value) And Dic(LastNameBox.Value).exists(LCase(FirstNameBox.Value)) Then
   MsgBox FirstNameBox.Value & " " & Me.LastNameBox.Value & " has obtained an EUC clearance, no further actions are necessary.", , "Match Found"
Else
   MsgBox "No match for " & FirstNameBox.Value & " " & Me.LastNameBox.Value & ", an EUC clearance must be obtained.", , "No Match Found"
End If
End Sub

 

[rickblunt]

Hi Fluff,
First let me sincerely apologize for not replying sooner, I had a catastrophic personal crisis just after I posted and I have been out of service for quite awhile. Finally back in the loop and so I am back on the project.

Creating an object is pretty cool, I have seen the technique but this is my first experience with actually using it. So the code worked great except for one little thing. If I enter in a value for the last name that is not on the last name list at all then I get an object error - if however I enter in a last name that is on the list but NOT paired up with the proper first name I will get the message saying that the name is not found, which is perfect. This doesn't have with the first name, just the last name though... I am still learning VBA, so I always try to figure it out myself before I ask. But I am having trouble finding what the snag is that is causing this. I see that you are referencing the "firstnamebox.value" but not the lastnamebox.value in this one particular string, is this the reason why?

If Dic.exists(LastNameBox.Value) And Dic(LastNameBox.Value).exists(LCase(FirstNameBox.Value)) Then

Thanks again for your assistance, it is really appreciated...

 

[Fluff]

The problem is, that if the last name does not exist in the dictionary, then this part

Dic(LastNameBox.Value).exists(LCase(FirstNameBox.Value))

will fail as there is no value in Dic that equates to Lastname, so it can't then check the FirstName.
Try this

Sub CheckName_Click()
   'This string looks to see if the person is in the database yet.
   If Len(LastNameBox) = 0 Or Len(FirstNameBox) = 0 Then
      MsgBox "You must enter both first & last names"
      Exit Sub
   End If
   If Dic.exists(LastNameBox.Value) Then
      If Dic(LastNameBox.Value).exists(LCase(FirstNameBox.Value)) Then
         MsgBox FirstNameBox.Value & " " & Me.LastNameBox.Value & " has obtained an EUC clearance, no further actions are necessary.", , "Match Found"
      Else
         MsgBox "No match for " & Me.FirstNameBox.Value & " " & Me.LastNameBox.Value & ", an EUC clearance must be obtained.", , "No Match Found"
      End If
   Else
      MsgBox "No match for " & Me.LastNameBox.Value & ", an EUC clearance must be obtained.", , "No Match Found"
   End If
End Sub

Another option, would be replace the 2 textboxes, with comboboxes, which will only allow you to enter names already on the sheet.

 

[rickblunt]

Hi Fluff, yes, that makes sense - I thought it was something like that but I was struggling on how the code was seeing it. That tweak worked great and it shows me the process - this is working perfectly. Thank you very much for the assistance and even more for the knowledge that you have shared with me.

 

[Fluff]

Glad we could help & thanks for the feedback