Loop through Jagged Arrays?

[peter5678]

I have populated a variant that is initially 1D from a worksheet using the Range.Value function. By the Split function this array then become a jagged array leaving me with an array like:

<table border="0" cellpadding="0" cellspacing="0" width="107"><tbody><tr style="height:15.0pt" height="20"><td class="xl63" style="height:15.0pt;width:80pt" height="20" width="107">f,g,h,k,l</td> </tr> <tr style="mso-height-source:userset;height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">a,b,c,d</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">m,n,o</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">x,4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">i,y</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">r,t
...
</td> </tr> </tbody></table>
Now I'm not sure how I can loop through this array to work with individual values?

I tried a For loop but I don't know how to get Ubound to the number of columns of a given row. The numbers of columns varies between 0 and 20 (I'm guessing). As I only occasionally write code it could be very something simple so if anyone has a good reason for jagged arrays in VBA that might be enough.

For reference I am using it as part of a UDF and here is the part of the code I'm using to load it.

Dim ToGroups As Variant

ToGroups = Range(ToGroupsTop & ":" & ToGroupsBottom).Value

'Splits Togroups string into jagged array & fromgroup into array
'Main index starts at 1 and subarrays start at 0, Sorry!
For index1 = 1 To UBound(ToGroups)
ToGroups(index1, 1) = Split(ToGroups(index1, 1), ",")
Next

Thanks for taking a look at this. Any help/advice would be appreciated.

 

[pgc01]

Hi Peter
Welcome to the board

Try running this code:

Sub test()
Dim ToGroups As Variant
Dim rToGroupsTop As Range, rToGroupsBottom As Range
Dim lIndex1 As Long, lIndex2 As Long
 
Set rToGroupsTop = Range("A2")
Set rToGroupsBottom = Range("A4")
 
ToGroups = Range(rToGroupsTop, rToGroupsBottom).Value
 
'Splits Togroups string into jagged array & fromgroup into array
'Main index starts at 1 and subarrays start at 0, Sorry!
For lIndex1 = LBound(ToGroups) To UBound(ToGroups)
    ToGroups(lIndex1, 1) = Split(ToGroups(lIndex1, 1), ",")
Next
 
' loop through the array
For lIndex1 = LBound(ToGroups) To UBound(ToGroups)
    For lIndex2 = LBound(ToGroups(lIndex1, 1)) To UBound(ToGroups(lIndex1, 1))
        MsgBox "Element (" & lIndex1 & ",1)(" & lIndex2 & "): " & ToGroups(lIndex1, 1)(lIndex2)
    Next lIndex2
Next lIndex1
End Sub

I used for the test:


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">a,b</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">c</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">d,e,f</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=3 style="background:#9CF; padding-left:1em" > [Book1.xls]Sheet1</td></tr></table>

 

[pgc01]

P. S. In the case of the example, since you use the constant 1 for the column value in the first level of the array, it seems you know that the range with the values will always be a vertical vector.

If that's the case you can simplify the code using a unidimensional first level of the array instead of bidimensional.

Like:

ToGroups = Application.Transpose(Range(rToGroupsTop, rToGroupsBottom).Value)

 

[peter5678]

Thanks for getting back to me so quickly. I'll try these two pieces of code later on tonight.

You are right in the way you read the example. I ended up putting in the constant 1 for column value after some frustrating trial and error. It is probably a result of the way I used the filled ToGroups but I could only access the array (even before the split) when I included this. I didn't think I should have had to for what was (in my mind at least) a one-dimensional array but it made it work so I went with it.

P.s. Sorry for my nearly unreadable original post. I thought I had read it over but evidently I was not seeing straight.

 

[mirabeau]

Peter,

On reading this through, is it perhaps something like this code that you could use (tested on your data in post#1)

Sub jaggedarray()
Dim c(), i, j, k&
For Each i In Range("A2:A7")
    For Each j In Split(i, ",")
        k = k + 1
        ReDim Preserve c(1 To k)
     c(k) = j
Next j, i
Range("C2").Resize(k) = Application.Transpose(c)
End Sub

 

[peter5678]

Thanks to both of you for all your help.

It took me a little while to get back to it this stuff but thanks to your help my UDF is all up and running. In the end I used pieces of both solutions.

ToGroups = Application.Transpose(Range(rToGroupsTop, rToGroupsBottom).Value)

Proved to be turning point. While I still don't fully understand Range.Value everything now works and I can easily and flexibly address any pieces of the arrays that I would like.

All the best.

Peter