Copy text from column 1, merge with text inside columns 1-120

[SHBeck]

Hello,
I need to merge contents of cells in column 1 with content of cells in columns 2-120, for example,

red 1900 1901 1902 1903 1904 1905 1906 1907 ........ 2018 2019 2020
blue 1915 1916 1917 1918 1919 1920 1921 1922 ........ 2033 2034 2035
green 1906 1907 1908 1909 1910 1911 1912 1913 ........ 2024 2025 2026


needs to be changed to;

red red 1900 red 1901 red 1902 red 1903 red 1904 red 1905 red 1906 red 1907 ....... red 2018 red 2019 red 2020
blue blue 1915 blue 1916 blue 1917 blue 1918 blue 1919 blue 1920 blue 1921 blue 1922 .......blue 1923 blue 1924 blue 1925
green green 1906 green 1907 green 1908........................

Thank you for any help, Shawn

 

[SHBeck]

Hello Peter, I have one more small change I would like to make. Instead of adding the data from column 1 to the data in columns 1-120, I would like to add the data from column 1 to columns 2-120. Thanks again for all of your help

 

[SHBeck]

Also I need to add a clause that will keep it from adding data to the blank cells within the range, the amount of columns containing data varies per row. I would like to add the data from column 1 only to the cells containing values. Thank you

 

[Peter_SSs]

Hello Peter, I have one more small change I would like to make. Instead of adding the data from column 1 to the data in columns 1-120, I would like to add the data from column 1 to columns 2-120.

Unless you changed the code I posted, it already does only start at column 2. See the red text in the code below, the 2 represents the second column (B).

Also I need to add a clause that will keep it from adding data to the blank cells within the range, the amount of columns containing data varies per row. I would like to add the data from column 1 only to the cells containing values. Thank you

Add the blue lines of code where shown.

For j = [COLOR="#FF0000"][SIZE=4][B]2[/B][/SIZE][/COLOR] To LC
  [COLOR="#0000CD"]If Len(a(i, j)) Then[/COLOR]
    a(i, j) = a(i, j) & Suffix
  [COLOR="#0000CD"]End If[/COLOR]
Next j

 

[SHBeck]

Worked great, Thanks again for your help!

 

[SHBeck]

Hello Peter, first of all I would like to thank you for this macro, it has been very helpful! I wanted to ask if it was possible to add an input window upon run of the macro that would ask the column location of the source text and of the starting cell for the destination range? I am using it on several sheets and this would save time from having to adjust the code for each different layout. Thanks again!!

 

[Peter_SSs]

Try this. It has no checking that the user puts valid (or sensible) values into the input boxes.
The Input Boxes have default column letters of A and B. You can change or remove those if you want.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Merge_Text()<br>  <SPAN style="color:#00007F">Dim</SPAN> a<br>  <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, SC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, FMC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> Suffix <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, SourceCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, FirstMergeCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>  <br>  LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row<br>  LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column<br>  SourceCol = Application.InputBox("Input source column label", , "A")<br>  SC = Columns(SourceCol).Column<br>  LC = LC - SC + 1<br>  FirstMergeCol = Application.InputBox("Input first column to merge into", , "B")<br>  FMC = Columns(FirstMergeCol).Column - SC + 1<br>  <SPAN style="color:#00007F">With</SPAN> Cells(1, SC).Resize(LR, LC)<br>    a = .Value<br>    <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> LR<br>      Suffix = " " & a(i, 1)<br>        <SPAN style="color:#00007F">For</SPAN> j = FMC <SPAN style="color:#00007F">To</SPAN> LC<br>          <SPAN style="color:#00007F">If</SPAN> Len(a(i, j)) <SPAN style="color:#00007F">Then</SPAN><br>            a(i, j) = a(i, j) & Suffix<br>          <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> j<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    .Value = a<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[SHBeck]

Thank you! It worked perfect. Also I am learning how these work by the changes we have made. Thanks again

 

[Peter_SSs]

Also I am learning how these work by the changes we have made.

That's the way to go.