Hi,
Could you please help me to create macro to add Public words at the end of names as below.
Public words: Corp Inc. Company CO Corporation co,ltd
Name: Apple
Macro result:
Apple Corp
Apple Inc.
Apple Company
Apple Corporation
Apple co,ltd
Please check attached file to get sample.
Thanks;
Ehab
Try this
Sub AddPublicWord()
Dim CoArray() As Variant
Dim PWArray() As Variant
Dim r As Long
Dim s As Long
Dim i As Long
i = 2
CoArray = Sheets("Sheet1").Range("A2:A4").Value
PWArray = Sheets("Sheet1").Range("B2:B7").Value
For r = 1 To UBound(CoArray, 1)
For s = 1 To UBound(PWArray, 1)
Cells(i, 5) = CoArray(r, 1) & " " & PWArray(s, 1)
i = i + 1
Next s
Next r
End Sub
You are amazing! Thank you so much!
I just want to repeat the name as below.
| Name | Public words | New Name |
| Apple | Corp | Apple Corp |
| Apple | Inc. | Apple Inc. |
| Apple | Company | Apple Company |
| Apple | CO | Apple CO |
| Apple | Corporation | Apple Corporation |
| Apple | co,ltd | Apple co,ltd |
Please check attached file.
Hi,
Just concatenate the strings e.g. in C2
=A2&" "&B2
See attached.
Regards
Phil
Hi Phil & Sunny,
Thank you very much
I just want to update the macro by adding new column and will contain the Original Name "before we add the public words".
I don't to repeat any value in column B.
Please check attached file.
Thank;
Ehab
Try this
Sub AddPublicWord()
Dim CoArray() As Variant
Dim PWArray() As Variant
Dim r As Long
Dim s As Long
Dim i As Long
i = 2
CoArray = Sheets("Sheet1").Range("A2:A4").Value
PWArray = Sheets("Sheet1").Range("B2:B20").Value
For r = 1 To UBound(CoArray, 1)
For s = 1 To UBound(PWArray, 1)
Cells(i, 5) = CoArray(r, 1) & " " & PWArray(s, 1)
Cells(i, 6) = CoArray(r, 1)
i = i + 1
Next s
Next r
End Sub
Wow! Thank you so much. This is awesome. This will save me so much time.
