CODEWITHSUNDEEP
excelvba
DevNull
DevNull

Reputation: 9

Select a range and then concatenate

I am trying to select a range, and then concatenate. I've scoured posts talking about UDFs, using join/concat/transpose/etc.

Sub ConcatThings()
    Dim str() As String
    Dim myRng As Range
    Set myRng = Application.Selection
    Set myRng = Application.InputBox("Select range of Stuff", "Select Stuff", "Maker of things", myRng.Address, Type:=8)

    For Each myCell In myRng
        If Len(myCell) Then
            str = Join(myCell, ",")
            ThisWorkbook.Sheets("general_report").Cells(2, 7) = str
        End If
    Next
End Sub

I'd like to print the values to the general_report sheet in cell 2,7.

I get a

Cant assign to array

It highlights

str = Join(myCell, ",")

Here is a mockup of the sheet (all values were pulled out of the air)

_| A  | B  | C  | D  | E  | 
1|Jan | 2  | Grn| OSX|Bird|
2|Mar | 4  | Blu| PC |Frog|
3|Feb | 6  | Red| OSX|Dogs|
4|Nov | 0  | Wht| And|Cats|
5|Jun | 1  | Ylw| iOS|Worm|

When you run the macro, you would select whichever column is in question, and it would print the following in cell 2,7 Grn,Blu,Red,Wht,Ylw

Upvotes: 0

Views: 156

Answers (1)

John Coleman
John Coleman

Reputation: 51988

As others have said in the comments, this is a straightforward TEXTJOIN situation, though it is complicated by your apparent need to not join potentially blank cells in the selected range. As @JvdV mentions, using SpecialCells is one way to address that complication, or you could more naively do something like:

Sub ConcatThings()
    Dim str() As String
    Dim n As Long
    Dim myRng As Range, myCell As Range

    Set myRng = Selection
    Set myRng = Application.InputBox("Select range of Stuff", "Select Stuff", myRng.Address, Type:=8)

    ReDim str(1 To myRng.Cells.count)

    For Each myCell In myRng
        If Len(myCell.Value) > 0 Then
            n = n + 1
            str(n) = myCell.Value 
        End If
    Next myCell
    ReDim Preserve str(1 To n)
    ThisWorkbook.Sheets("general_report").Cells(2, 7).Value = Join(str, ",")
End Sub

Note that the assignment to Cells(2,7) goes outside of the loop.

Upvotes: 1

Related Questions