Finding Customers with the same name

[Dan Swartz]

I have a userform for pulling a customer's information. it works great. But if I have two or more customers with the same name. It only finds the first name. I live in a community of Amish. It's not uncommon to have 5 - 10 customers with the same name.

I don't even know where to start.

Is there a way to search for a name, display all customers with the same name, but show their address so i would know which customer is the correct one and then be able to choose that customer?

This is my code for the current Customer Form.

Private Sub FindCust_Click()
    
  Dim f As Range
  
  If CustomerName.Value = "" Then
    MsgBox "Please enter a customer 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]

Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

Sure. But it will take me time. I need to strip out a lot of stuff. give me till tomorrow and I'll get that done!

 

[Akuini]

Ok, good.
I also need you to explain in more detail what you are trying to accomplish. Loading data into a userform typically serves four main purposes:

1. Displaying information.
2. Editing/updating existing records.
3. Deleting existing records.
4. Creating new records.

Which of these purposes do you want to achieve?

 

[Dan Swartz]

Ok, good.
I also need you to explain in more detail what you are trying to accomplish. Loading data into a userform typically serves four main purposes:

1. Displaying information.
2. Editing/updating existing records.
3. Deleting existing records.
4. Creating new records.

Which of these purposes do you want to achieve?

Great questions. I should have answered them in my original post!

I'm wanting to do 1, 2, & 4. I have 4 figured out. But for 1 & 3. it's more difficult when dealing with multiple customers.

 

[Dan Swartz]

Ok @Akuini Here is the link. I think I did the correctly. I had a lot of propriety things strip out. I hope this helps.
Customer Database

 

[Akuini]

Ok @Akuini Here is the link. I think I did the correctly. I had a lot of propriety things strip out. I hope this helps.
Customer Database

Thanks, I've downloaded your file, but I don't see any customer ID column. I thought you had already decided to add it to the database.

 

[Dan Swartz]

Thanks, I've downloaded your file, but I don't see any customer ID column. I thought you had already decided to add it to the database.

Well I did.....I had a bad night. My computer crashed. I lost most of my data as it corrupted my file. And then just shut it down for the night in disgust! I forgot to add that back in. I'm needing to reset my computer. I plan to do that today. All I did was add the column. I didn't do any code yet.

 

[Akuini]

Well I did.....I had a bad night. My computer crashed. I lost most of my data as it corrupted my file.

Sorry to hear that.

I plan to do that today. All I did was add the column. I didn't do any code yet.

In what column the customer ID would go? Unique identifier is usually in col A.

 

[Dan Swartz]

Sorry to hear that.


In what column the customer ID would go? Unique identifier is usually in col A.

Yes. I had the column Heading as Customer ID and had that data in Column A

 

[Akuini]

Ok, I'll get back to you tomorrow.
What's Customer ID looks like?

 

[Dan Swartz]

Ok, I'll get back to you tomorrow.
What's Customer ID looks like?

I will be on vacation from tomorrow till next Tuesday. I won't have much signal. I'll try and pop on to see what you got. CustomerID was C00001