VBA help, merge City, State

[brent3162]

Simple for some, not for me. I have a form where I have a list tied to a named range worksheet called customer (see attached). When I select a customer it populates the form with the address of that customer just below the customer name. Then, below the address add the same customers phone number prefaced with "PH:" [PhoneNumber]. I'm struggling with bringing all the address cells together into one field using VBA as well as placing the phone number on the next row below address. I know how to merge the cell for all the address fields using the concatenate function within excel but can't figure out how to do this with VBA.

Also I'm using excel on MacOS and am trying to add this code to run in the background so that when I change the customer name it automatically updates these two rows with the correct address/phone#

 

[jolivanes]

Re: "bringing all the address cells together into one field"
Strongly advice against that. I would keep it simple and just have the address and phone number in cells below the name.

Excel Tips: Why You Should Avoid Merging Cells

 

[brent3162]

In general, I agree with your response. However in this case, I'm not using the merged content for anything other than adding it to an INVOICE to be printed out that has the customer name, address and phone number on it (see the first attachment). I do not plan on saving this merged value anywhere except on the invoice to be printed. As you can see from my customer worksheet, I already have them separated in their own columns. So don't give up on me yet.

 

[Alex Blakenburg]

If your customer list is an Excel Table (ie listobject) then something like this might work for you.
Change Table1 to your table name.

Sub FillInAddress()

    Dim tbl As ListObject, rowTbl As Long
    Dim sMailAdd As String, sPhone As String
    Dim wsInvoice As Worksheet
    
    Set wsInvoice = Worksheets("INVOICE")
    Set tbl = Range("Table1").ListObject            ' Change to your Table name
    
    With Application
        rowTbl = .IfError(.Match(wsInvoice.Range("C7"), tbl.ListColumns(1).DataBodyRange, 0), 0)
    End With
    
    If rowTbl = 0 Then
        MsgBox "Customer not found"
        Exit Sub
    End If
    
    With tbl.ListColumns("MAILING ADDRESS").DataBodyRange(rowTbl)
        sMailAdd = .Value _
                    & ", " & .Offset(, 1).Value _
                    & ", " & .Offset(, 2).Value _
                    & ", " & .Offset(, 3).Value
        sPhone = "PH: " & tbl.ListColumns("PHONE").DataBodyRange(rowTbl).Text
    End With
                
    wsInvoice.Range("C8") = sMailAdd
    wsInvoice.Range("C9") = sPhone
    
End Sub

 

[brent3162]

@Alex Blakenburg , thanks for the code, see attached screen capture for the error I'm getting. I should state that the customer is NOT an excel table but just a range called "Customer_List". So I replaced the table name in your code with this rangeName. Also is there a way to have this function ran each time automatically when the customer name is changed? that is what I was trying to do with the worksheet change on event option that seems to be broken on the Mac version of 365 Excel.

 

[Alex Blakenburg]

If you use a table it automatically expands and contracts. If you use a range name then you have to update the range name.
Is that going to be reliable ? Otherwise you are better off not to use the named range.
Not using a table will totally change the way the code works.

 

[Alex Blakenburg]

Here is a version not using a table. You will need to change the Customer List sheet name to your real sheet name.
You will need to put it in the Sheet Module of Invoices (ie right click on the Invoices tab and "View Code")

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wsCust As Worksheet
    Dim rngCust As Range, rowCust As Long, rowLastCust As Long
    Dim sMailAdd As String, sPhone As String
    
    If Target.Address = "$C$7" Then
        Application.EnableEvents = False
        
        Set wsCust = Worksheets("CUSTOMER LIST")     ' Change to your sheet name
        
        With wsCust
            rowLastCust = .Range("A" & Rows.Count).End(xlUp).Row
            Set rngCust = .Range("A2", .Cells(rowLastCust, "G"))
        End With
        
        With Application
            rowCust = .IfError(.Match(Me.Range("C7"), rngCust.Columns(1), 0), 0)
        End With
        
        If rowCust <> 0 Then
        
            With rngCust.Columns(4).Cells(rowCust)
                sMailAdd = .Value _
                            & ", " & .Offset(, 1).Value _
                            & ", " & .Offset(, 2).Value _
                            & ", " & .Offset(, 3).Value
                sPhone = "PH: " & rngCust.Columns(2).Cells(rowCust).Text
            End With
                        
            Me.Range("C8") = sMailAdd
            Me.Range("C9") = sPhone
        
        Else
            MsgBox "Customer not found"
        End If
        Application.EnableEvents = True
    End If

End Sub

 

[jolivanes]

The way I understand it.
Check and change spelling, capitalization, references where required.
Copy this into the module of the "INVOICE" sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("C7")) Is Nothing Then Get_Data
End Sub

and the following in a regular module

Sub Get_Data()
    Range("C8").Value = Sheets("CUSTOMER").Columns(1).Find(Range("C7").Value, , , 1).Offset(, 3).Value
    Range("C9").Value = Sheets("CUSTOMER").Columns(1).Find(Range("C7").Value, , , 1).Offset(, 1).Value
End Sub

 

[brent3162]

The way I understand it.
Check and change spelling, capitalization, references where required.
Copy this into the module of the "INVOICE" sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("C7")) Is Nothing Then Get_Data
End Sub

and the following in a regular module

Sub Get_Data()
    Range("C8").Value = Sheets("CUSTOMER").Columns(1).Find(Range("C7").Value, , , 1).Offset(, 3).Value
    Range("C9").Value = Sheets("CUSTOMER").Columns(1).Find(Range("C7").Value, , , 1).Offset(, 1).Value
End Sub

@jolivanes while the code ran, it was missing data. It added the Address to C8, but was missing the ", state" and "zipcode". Also on the C9 row while the phone number was returned it was somewhat indented to the right with no "PH:" prefix.

 

[jolivanes]

Replace the "Get_Data" macro with following.

Sub Get_Data()
    Range("C8").Value = Join(Application.Transpose(Application.Transpose(Sheets("CUSTOMER").Columns(1).Find(Range("C7").Value, , , 1).Offset(, 3).Resize(, 4))), ", ")
    Range("C9").Value = "Ph: " & Sheets("CUSTOMER").Columns(1).Find(Range("C7").Value, , , 1).Offset(, 1).Value
End Sub