VBA Cant assign to array

Question

I am still very new with VBA and wouldn't put my hand up and say I'm good at this at all, 99% of everything I've made so far has been tossed together from other solutions found here (mostly) and elsewhere. I am trying to select a range, and then concatenate. Sounds simple enough and I've scoured a lot of posts talking about UDFs, using join/concat/transpose/etc.

Code I'm working with at the moment.

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 this to print the values to the general_report sheet in cell 2,7.
When I give it a go, I get a "Cant assign to array" and it highlights the str = Join portion.
What am I doing wrong?

Update 1 - Here is a mockup of the sheet I am working with (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

`Join` returns a `String`, not an array of strings.... but I would suggest taking a look at this [TEXTJOIN UDF](https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell) for an already existing approach to do what you want. — BigBen, May 27 '20 at 13:51
What is `Join(myCell, ",")` supposed to do? It doesn't make obvious sense to join together the contents in a single cell. — John Coleman, May 27 '20 at 14:00
@John, from what I understood, I was looping through all the cells selected from the InputBox, joining the content with a "," character and then adding it to cell 2,7. — DevNull, May 27 '20 at 14:04
Seems like you not interested in returning a `Range` object at `Set myRng` but an array of values instead. That variable is then ready to be joined using `Join()`. See link provided by Ben, you could even toss in `SpecialCells` to prevent a loop over non-empty cells. — JvdV, May 27 '20 at 14:05

score 1 · Accepted Answer · answered May 27 '20 at 14:19

1

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.

answered May 27 '20 at 14:19

John Coleman

51,337
7
54
119

Thank you very much for your assistance. Your answer works. I'd like to understand a bit more if you dont mind. Could you expand on two comments? The first, "it is complicated by your apparent need to not join potentially blank cells in the selected range" as I am not sure where/how I implied that in the original code? The second, "your InputBox is ill-formed" What exactly is ill-formed about it? The majority of it from what I saw was optional from what I read https://learn.microsoft.com/en-us/office/vba/api/excel.application.inputbox – DevNull May 27 '20 at 14:35
1

@DevNull - check the arguments... `"Maker of things"` seems in the wrong place. – BigBen May 27 '20 at 14:42
@BigBen I think you are correct, I should not have entered data there for the sake of 'filling' out the blank text box when it is drawn. – DevNull May 27 '20 at 14:47

VBA Cant assign to array

1 Answers1