loop through a multidimensional array in vba excel

Question

I'm trying to loop through a 2d array. The 1d will always be 25, the 2d will have different amounts. Quite often the members of the 1st dimensional will be empty which is the point of the isarray(sent) code. I'm getting a subscript out of range at the part which says for j = 1 to ubound(sent,2)

For i = 1 To 25
    If IsArray(sent(i)) Then
        For j = 1 To UBound(sent, 2)
            If concat_multi = "" Then
            concat_multi = sent(i, j)
            Else
            concat_multi = concat_multi & " & " & sent(i, j)
            End If
        Next
        ActiveCell.Offset(1) = concat_multi
        concat_multi = ""
    End If
Next

Here is a screenshot

enter image description here

Look at your object broser. You need to refer to it as Sent(i)(j) rather than sent(i, j). — David Zemens
– David Zemens, Commented Oct 14, 2014 at 3:26
but the error I'm getting is in this part of the code For j = 1 To UBound(sent, 2) and I cannot rewrite that line of code as for j = 1 to ubound(sent(i)(j)) — user147178
– user147178, Commented Oct 14, 2014 at 3:36

David Zemens · Accepted Answer · 2014-10-14 03:48:44Z

5

Look at your object browser. You need to refer to it as Sent(i) is a 1d array. So you have a 1d array wherein each element is another 1d array.

rather than sent(i, j) do sent(i)(j) and initiate the loop thusly:

for j = 1 To ubound(sent(i))

Technically you should probably be doing this in case you get arrays with base other than 1 (base 0 is common and default unless it is a range array).

For j = lbound(sent (i)) to ubound(sent(i))

edited Oct 14, 2014 at 3:48

answered Oct 14, 2014 at 3:27

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

loop through a multidimensional array in vba excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related