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#

 

[brent3162]

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

@jolivanes yes, this fixed the issue. Thanks so much. Now I have two solutions to my problem. You guys ROCK!!!