Concatenate to rows with VBA

[slohman]

I am using this code and its working great but I need to know if I can join 2 columns together. I need column A and E columns to join together then a comma in between the next A and E something that looks like this

FS15 - Backhoe, FS105 - Badminton Net Free Standing, FS22 - Ball Toss

Sub MoveOption_to_Cost()
Dim SheetName As String
SheetName = "Option1"
SheetName = InputBox("Enter Option Number - this will update Cost Worksheet", "sheet name", SheetName)
Dim i As Long
Dim LastRow As Long
Dim myCol As Integer
Dim MyRow As Integer
Dim myRange As Range
Dim cell As Object
Dim wsActive As Worksheet
Dim TopRow As Long, BottomRow As Long, FoundRow As Long
Dim ResetRange As Range
Set wsActive = ActiveSheet ' The worksheet
                       
Application.ScreenUpdating = False

LR = Sheets(SheetName).Range("G" & Rows.Count).End(xlUp).Row
myCol = 2
MyRow = 30
    For i = 215 To 1023
        If Val(Sheets(SheetName).Range("F" & i).Value) >= 1 Then
            Do Until Sheets("Cost").Cells(MyRow, myCol).Value = "" Or MyRow > 78
                MyRow = MyRow + 1
            Loop
            If MyRow <= 78 Then
                Sheets("Cost").Cells(MyRow, myCol).Value = Sheets(SheetName).Range("A" & i).Value
                MyRow = MyRow + 1
            Else
                MsgBox "You have ran out of room.  Some entries were not copied"
                    Exit For
                End If
            End If
    Next i

LR = Sheets(SheetName).Range("G" & Rows.Count).End(xlUp).Row
myCol = 4
MyRow = 30
    For i = 215 To 1023
        If Val(Sheets(SheetName).Range("F" & i).Value) >= 1 Then
            Do Until Sheets("Cost").Cells(MyRow, myCol).Value = "" Or MyRow > 78
                MyRow = MyRow + 1
            Loop
            If MyRow <= 78 Then
                Sheets("Cost").Cells(MyRow, myCol).Value = Sheets(SheetName).Range("E" & i).Value
                MyRow = MyRow + 1
            Else
                MsgBox "You have ran out of room.  Some entries were not copied"
                    Exit For
                End If
            End If
    Next i


Application.ScreenUpdating = False
           
  
Sheets("Cost").Range("B83").Select

Application.ScreenUpdating = True

End Sub

 

[NARAYANK991]

Hi ,

Do you want to join everything in columns A and E to form one giant text string , or do you want every cell in the same row joined each time ?

In the second case , A2 and E2 would form one text string , A3 and E3 would form one text string , and so on till the last row of data.

Narayan

 

[slohman]

I have put an example on the JobOrder worksheet under column K2

 

[NARAYANK991]

Hi ,

Can you please recheck the workbook you have uploaded ?

The JobOrder worksheet tab does not have anything like what you have posted in your initial post.

Also , can you mention which module contains the code you want to modify ?

Narayan

 

[slohman]

Module 11 is the code I wish to modify. You will also see the example I have put in the file is I would love to change the Site and Location into 2 columns.

 

[slohman]

Can anyone help with this?