Add text from one cell to the beginning of a range of cells in the same row (if those cells have a value)

[DebN]

Hello I know next to nothing about VBA code and would really appreciate some help.
I would like to know if it is possible to add the text of one cell (for example a name in cell B2) to the beginning of a range of other cells (for example C2 to G2) if those cells have a value entered in them.
I would like to do this for rows 2 to 10, placing the text from the B column of that row at the front of the cells in that same row (cells C-G)

A	B	C	D	E	F	G
	Joe			1230	1200	1200
	Mary	830	845	830	845	845
	John	845	950		950	950
	When I run the macro, I would like it to change to:
	Joe			Joe1230	Joe1200	Joe1200
	Mary	Mary830	Mary845	Mary830	Mary845	Mary845
	John	John845	John950		John950	John950

Thanks so much to anyone willing to help me out.

 

[Ezguy4u]

Just to get the ball rolling. Now I can put together a basic program. Maybe some of the A Students will weigh in and tighten up the program. If you have any questions please let us know

Sub Join1()
Dim LastRow  As Long
Dim Row1 As Long
Dim Col1 As Long

LastRow = Cells(Rows.Count, "B").End(xlUp).Row

For Row1 = 2 To LastRow

    For Col1 = 3 To 7
        If Cells(Row1, Col1) <> "" Then
            Cells(Row1, Col1) = Cells(Row1, 2) & Cells(Row1, Col1)
        End If
    Next Col1

Next Row1

End Sub

20-09-21 join cells.xlsm
	A	B	C	D	E	F	G	H	I
1
2		Joe			Joe1230	Joe1200	Joe1200
3		Mary	Mary830	Mary845	Mary830	Mary845	Mary845
4		John	John845	John950		John950	John950
5
6				When I run the macro, I would like it to change to:
7				Joe			Joe1230	Joe1200	Joe1200
8				Mary	Mary830	Mary845	Mary830	Mary845	Mary845
9				John	John845	John950		John950	John950
Join Text and Numbers

 

[jolivanes]

Try

Sub Maybe_So()
Dim c As Range, cel As Range
    For Each c In Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
        For Each cel In c.Offset(, 1).Resize(, Cells(c.Row, Columns.Count).End(xlToLeft).Column - 2).SpecialCells(2)
            cel.Value = c.Value & cel.Value
        Next cel
    Next c
End Sub

 

[jolivanes]

Read your requirement again.
Change the B1 to B2 as you want to start at the 2nd row.
If your row10 is a hard number, change that whole line to

For Each c In Range("B2:B10")

The way it is in Post #3 is more versatile. It doesn't matter how much data is in Column B.

 

[Rick Rothstein]

One more way (no loops)...

Sub test()
  With Range("C1", Cells(Cells(Rows.Count, "B").End(xlUp).Row, Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column))
    .Value = Evaluate(Replace("IF(@="""","""",B1:B" & .Rows.Count & "&@)", "@", .Address))
  End With
End Sub

 

[DebN]

Just to get the ball rolling. Now I can put together a basic program. Maybe some of the A Students will weigh in and tighten up the program. If you have any questions please let us know

Sub Join1()
Dim LastRow  As Long
Dim Row1 As Long
Dim Col1 As Long

LastRow = Cells(Rows.Count, "B").End(xlUp).Row

For Row1 = 2 To LastRow

    For Col1 = 3 To 7
        If Cells(Row1, Col1) <> "" Then
            Cells(Row1, Col1) = Cells(Row1, 2) & Cells(Row1, Col1)
        End If
    Next Col1

Next Row1

End Sub

20-09-21 join cells.xlsm
	A	B	C	D	E	F	G	H	I
1
2		Joe			Joe1230	Joe1200	Joe1200
3		Mary	Mary830	Mary845	Mary830	Mary845	Mary845
4		John	John845	John950		John950	John950
5
6				When I run the macro, I would like it to change to:
7				Joe			Joe1230	Joe1200	Joe1200
8				Mary	Mary830	Mary845	Mary830	Mary845	Mary845
9				John	John845	John950		John950	John950
Join Text and Numbers

Wow, it works! Thanks so much... I can't believe you are so kind (to write it for me).... Thanks!

 

[DebN]

Read your requirement again.
Change the B1 to B2 as you want to start at the 2nd row.
If your row10 is a hard number, change that whole line to

For Each c In Range("B2:B10")

The way it is in Post #3 is more versatile. It doesn't matter how much data is in Column B.

You people are amazing... thanks - this worked!

 

[DebN]

One more way (no loops)...

Sub test()
  With Range("C1", Cells(Cells(Rows.Count, "B").End(xlUp).Row, Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column))
    .Value = Evaluate(Replace("IF(@="""","""",B1:B" & .Rows.Count & "&@)", "@", .Address))
  End With
End Sub

Wow - excellent - thank you, thank you, thank you!.... This code is excellent. Works like a charm.

 

[jolivanes]

Thank you for taking the time to let us know that you were satisfied with the suggestions.
Wish all people were that well brought up.