need help using a for loop to concatenate cells

[Learner99]

Hi everyone.
I'm trying to concatenate a few cells but seem to be missing the mark with my code.
Can you show me where I went wrong.
Thank you

Dim counter As Long
Dim b As Range
Dim e As Range
Dim g As Range

 Range("E1").Select                             'Set the range for the loop
    Selection.End(xlDown).Select

    Set b = Range("B1:" & Selection.Address)   'destination

    Set e = Range("E1:" & Selection.Address)   'name Part1
    Set g = Range("F1:" & Selection.Address)   'name Part2
    
    For n = 1 To e.Rows.Count                   'This is where you set the column to sort on
       b.Cells(n, 1) = e.Cells(n, 1) &" " &g.Cells(n, 1)    ' <--Trying to get this to concatenate
     Next n

 

[JLGWhiz]

Hi everyone.
I'm trying to concatenate a few cells but seem to be missing the mark with my code.
Can you show me where I went wrong.
Thank you

Dim counter As Long
Dim b As Range
Dim e As Range
Dim g As Range

 Range("E1").Select                             'Set the range for the loop
    Selection.End(xlDown).Select

    Set b = Range("B1:" & Selection.Address)   'destination

    Set e = Range("E1:" & Selection.Address)   'name Part1
    Set g = Range("F1:" & Selection.Address)   'name Part2
    
    For n = 1 To e.Rows.Count                   'This is where you set the column to sort on
       b.Cells(n, 1) = e.Cells(n, 1) &" " &g.Cells(n, 1)    ' <--Trying to get this to concatenate
     Next n

Add a little value to your work:

For n = 1 To e.Rows.Count
       b.Cells(n, 1) = e.Cells(n, 1).Value & " " & g.Cells(n, 1).Value
Next n

 

[Learner99]

Thank you for the feed, back but it didnt work.
I'm still geeting a repeat of cell E .

 

[JLGWhiz]

Maybe this will help you to sort it out.
1.

Range("E1").Select                             
    Selection.End(xlDown).Select

If there are blank cells in column E before the last cell with data, the selected cell will be in the row before the blank cell. Otherwise the last cell in the range will be the last cell with data in column e. For reference, this will be 'lastcell'.
2.

Set b = Range("B1:" & Selection.Address)

This range would be B1:E & lastcell
3.

Set e = Range("E1:" & Selection.Address)

This range would be E1:E & lastcell
4.

Set g = Range("F1:" & Selection.Address)

This Range would be E1:F & lastcell

So, this statement:

b.Cells(n, 1) = e.Cells(n, 1) &" " &g.Cells(n, 1) 
[/Code}
First iteration Translates to:
B1 = E1 & " " & E1
And it would follow right on down with 2, 3, 4... all concatenating E to E.

Try substituting this:
[Code]
Range("E1").Select                             'Set the range for the loop
    Selection.End(xlDown).Select
    Set b = Range("B1:" & Selection.Address)   'destination
    Set e = Range("E1:" & Selection.Address)   'name Part1
    Set g = Range("F1:" & Selection.Address)   'name Part2

With this:

Dim lr As Long
lr = Range("E" & Rows.count).End(xlUP) 'Gets last cell with data in column E.
Set b = Range("B1:B" & lr) 'column B only
Set e = Range("E1:E" & lr) 'Column E only
Set g = Range("F1:F" & lr) 'Column F only
'Then add the modified statement
For n = 1 To e.Rows.Count
       b.Cells(n, 1) = e.Cells(n, 1).Value & " " & g.Cells(n, 1).Value
Next n

 

[Learner99]

Thank you for the great explaination and the code assistance.
I tested your suggestion and got a type-missmatch.

 

[Learner99]

Hi everyone I got my answer. Thank you all for your help.
Here is the route I took.

Dim lcell As Long
For lcell = 1 To Range("E" & Rows.Count).End(xlUp).Row
    Range("B" & lcell).Value = Range("E" & lcell).Value & " " & Range("F" & lcell).Value
Next lcell