Pulling info from a Sheet into UserForm

[Dan Swartz]

I have a workbook with a customer database listing my customers. I have a user form on a different page to look for a customer in the customer database, and if it's there, it will fill out the form box with the information that is listed in the Customer database. once the info has been populated to the user form and I can change the information and have it right back to the database with the changes. (This code is working fine. I won't include it) Everything works as expected, but only for the first name in the database. It can't find anyone below the first name listed. it says "Customer does not exist" which is what it's supposed to do if it doesn't exist. However, it does exist and I'm sure it's something little that I'm missing. I'm new to vba. So be nice!

Private Sub FindCust_Click()
    
    Dim CustN As String
 
    CustN = CustomerName.Text
    
    lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 3 To lastrow
        If Sheet2.Cells(i, 1).Value = CustN Then
            Address1.Text = Sheet2.Cells(i, 2).Value
            Address2.Text = Sheet2.Cells(i, 3).Value
                City.Text = Sheet2.Cells(i, 4).Value
               State.Text = Sheet2.Cells(i, 5).Value
                 Zip.Text = Sheet2.Cells(i, 6).Value
               Email.Text = Sheet2.Cells(i, 7).Value
            Exit Sub
        
        Else
            MsgBox ("Customer does not exist"), vbOKOnly
            
            CustomerName.SetFocus
            
            Exit Sub
                        
    End If
            
Next
      
End Sub

 

[My Aswer Is This]

I think you should remove this line of code:
Exit Sub

 

[DanteAmor]

Try the Find method:

Private Sub FindCust_Click()
  Dim f As Range
  
  If CustomerName.Value = "" Then
    MsgBox "Enter name"
    CustomerName.SetFocus
    Exit Sub
  End If
  
  Set f = Sheet2.Range("A:A").Find(CustomerName.Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    Address1.Text = Sheet2.Cells(f.Row, 2).Value
    Address2.Text = Sheet2.Cells(f.Row, 3).Value
    City.Text = Sheet2.Cells(f.Row, 4).Value
    State.Text = Sheet2.Cells(f.Row, 5).Value
    Zip.Text = Sheet2.Cells(f.Row, 6).Value
    Email.Text = Sheet2.Cells(f.Row, 7).Value
  Else
    MsgBox ("Customer does not exist"), vbOKOnly
    CustomerName.SetFocus
  End If
End Sub

 

[Dan Swartz]

I think you should remove this line of code:
Exit Sub

Removing Exit Sub didn't work. Made it run straight to the "Customer does not exist box" even though it found it and it still would only find the first result.

 

[Dan Swartz]

Try the Find method:

Private Sub FindCust_Click()
  Dim f As Range
 
  If CustomerName.Value = "" Then
    MsgBox "Enter name"
    CustomerName.SetFocus
    Exit Sub
  End If
 
  Set f = Sheet2.Range("A:A").Find(CustomerName.Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    Address1.Text = Sheet2.Cells(f.Row, 2).Value
    Address2.Text = Sheet2.Cells(f.Row, 3).Value
    City.Text = Sheet2.Cells(f.Row, 4).Value
    State.Text = Sheet2.Cells(f.Row, 5).Value
    Zip.Text = Sheet2.Cells(f.Row, 6).Value
    Email.Text = Sheet2.Cells(f.Row, 7).Value
  Else
    MsgBox ("Customer does not exist"), vbOKOnly
    CustomerName.SetFocus
  End If
End Sub

This worked great! thank you for the answer