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]

Another thing that I forgot:
I should dim tbl_CustomerDB as ListObject instead of Object, so in the code (post#34) :
Change this part:

Private tbl_CustomerDB As Object

to this:

Private tbl_CustomerDB As ListObject

Good Morning @Akuini! I am FINALLY getting back to working on this. I have imported your code to my production sheet. it works great. but as you stated, it wouldn't be bad to include phone numbers. I added 3 columns to the CustomerDB, Landline, Cell1, and Cell2. I went to the Private Sub Userform_Initialize() and added the columns to the aryheader. but I now get an error the combobox1 change. I wish I understood what was going on so I could make modifications. Could you help me?

 

[Dan Swartz]

Sorry, I forgot to refer to the table CustomerDB properly. Use this one instead:

Sub new_customer_code()
Dim cn As String
Dim c As Range
With Sheets("CustomerDB").ListObjects(1).DataBodyRange
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes 'Make sure that table CustomerDB is sorted ascending by col A
    Set c = .Columns(1).Find(What:="C*", LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
End With
cn = "C" & Format(Val(Mid(c.Value, 2)) + 1, "00000") 'create new customer code
Debug.Print cn
End Sub

I have tried this code. It is finding the last number in the CustomerDB, but won't write the new number to the CustomerDB. What am I doing wrong?

 

[Akuini]

I added 3 columns to the CustomerDB, Landline, Cell1, and Cell2. I went to the Private Sub Userform_Initialize() and added the columns to the aryheader. but I now get an error the combobox1 change. I wish I understood what was going on so I could make modifications. Could you help me?

I have tried this code. It is finding the last number in the CustomerDB, but won't write the new number to the CustomerDB. What am I doing wrong?

Could you share your updated workbook?
I will provide a more detailed explanation of how the code works later on.

 

[Dan Swartz]

Could you share your updated workbook?
I will provide a more detailed explanation of how the code works later on.

There is proprietary information in already. I'll save a copy and strip everything out. this will take some time. So I'll try and get it tomorrow or Tuesday.

 

[Dan Swartz]

There is proprietary information in already. I'll save a copy and strip everything out. this will take some time. So I'll try and get it tomorrow or Tuesday.

@Akuini I finally got this striped out. Here is the file. I added Landline, Cell1 and Cell2 columns to the DB. then I added them to aryheader. but it then throws and error. I'm assuming I have to make some other code adjustment, but I don't understand the code enough to find where that is.
Dan's Customer Database

 

[Akuini]

Dan's Customer Database

I can't download the file, access denied.

 

[Dan Swartz]

I can't download the file, access denied.

Hmmmmm Try it now?? Dan's Customer Database I shared it the same way I did last time.

 

[Akuini]

Ok, the last link works.

 

[Akuini]

You amended the code in this part correctly:

aryHeader = Split("\Code\CustomerName\Address1\Address2\City\State\Zip\Email\Landline\Cell1\Cell2", "\") 'array of textboxes name

However, this "aryHeader" is actually to get the textboxes name, because you named the textboxes base on the headers, such as Code, CustomerName, etc. Now, you've added new columns which are LandLine, Cell1, Cell2. So you need (but you haven't done it yet) to add 3 new textboxes in the userform and name them accordingly.

I amended "Sub populate_Textbox" a bit and added some comments. I also added debug.print line so you can see the values of some variable in the immediate windows. Just don't forget to remove debug.print line when you no longer need them.
To better understand a VBA code, you need to learn how to debug. There are plenty of resources on this topic available on YouTube, just search "excel vba debug"

Sub populate_Textbox()
Dim c As Range, f As Range
Dim va
Dim i As Long
        'find cell in col A that match sCode, sCode get value from Sub ListBox1_Click
        Set c = tbl_CustomerDB.DataBodyRange.Columns(1).Find(sCode, LookIn:=xlValues, _
        lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
        If Not c Is Nothing Then
Debug.Print sCode & " located in cell: " & c.Address
            va = c.Resize(1, UBound(aryHeader))  'populate records in c row to va
            
            For i = 1 To UBound(aryHeader)
                Me.Controls(aryHeader(i)).Text = va(1, i)
Debug.Print "i = " & i & " ; " & "aryHeader(i) = " & aryHeader(i) & " ; " & "va(1, i) = " & va(1, i)
            Next
        End If
        sCode = Empty
End Sub

I explain it a bit:
when i = 1 it will be:
Me.Controls(aryHeader(1)).Text = va(1, 1)
aryHeader(1) is "Code"
Me.Controls("Code") means a control named "Code" (in this case is the "Code" textbox)
va(1, 1) is sCode

when i = 2 it will be:
Me.Controls(aryHeader(2)).Text = va(1, 2)
aryHeader(2) is "CustomerName"
Me.Controls("CustomerName") means a control named "CustomerName" (in this case is the "CustomerName" textbox)
va(1, 2) is CustomerName (col B) for the sCode

 

[Dan Swartz]

You amended the code in this part correctly:

aryHeader = Split("\Code\CustomerName\Address1\Address2\City\State\Zip\Email\Landline\Cell1\Cell2", "\") 'array of textboxes name

However, this "aryHeader" is actually to get the textboxes name, because you named the textboxes base on the headers, such as Code, CustomerName, etc. Now, you've added new columns which are LandLine, Cell1, Cell2. So you need (but you haven't done it yet) to add 3 new textboxes in the userform and name them accordingly.

I amended "Sub populate_Textbox" a bit and added some comments. I also added debug.print line so you can see the values of some variable in the immediate windows. Just don't forget to remove debug.print line when you no longer need them.
To better understand a VBA code, you need to learn how to debug. There are plenty of resources on this topic available on YouTube, just search "excel vba debug"

Sub populate_Textbox()
Dim c As Range, f As Range
Dim va
Dim i As Long
        'find cell in col A that match sCode, sCode get value from Sub ListBox1_Click
        Set c = tbl_CustomerDB.DataBodyRange.Columns(1).Find(sCode, LookIn:=xlValues, _
        lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
        If Not c Is Nothing Then
Debug.Print sCode & " located in cell: " & c.Address
            va = c.Resize(1, UBound(aryHeader))  'populate records in c row to va
           
            For i = 1 To UBound(aryHeader)
                Me.Controls(aryHeader(i)).Text = va(1, i)
Debug.Print "i = " & i & " ; " & "aryHeader(i) = " & aryHeader(i) & " ; " & "va(1, i) = " & va(1, i)
            Next
        End If
        sCode = Empty
End Sub

I explain it a bit:
when i = 1 it will be:
Me.Controls(aryHeader(1)).Text = va(1, 1)
aryHeader(1) is "Code"
Me.Controls("Code") means a control named "Code" (in this case is the "Code" textbox)
va(1, 1) is sCode

when i = 2 it will be:
Me.Controls(aryHeader(2)).Text = va(1, 2)
aryHeader(2) is "CustomerName"
Me.Controls("CustomerName") means a control named "CustomerName" (in this case is the "CustomerName" textbox)
va(1, 2) is CustomerName (col B) for the sCode

This is helpful! I'm going to add this into my existing sheet and hope I can get it right. Thank you for explaining the code. I'm actually in the middle of taking VBA classes. And the plan is to continue for a while. Thanks so much for your help!