How do i loop through and array of an array

Question

I am trying to create a loop in my code that checks a list of arrays (with strings stored in them) against the 'Circs' string variable (that is user-defined) and returns a value of true if the string exists in any of the arrays.

Have used Debug and other SO answers to try and understand what is going on. Tried using the Array() feature where CircsG1 = Array()...... which just causes the error to appear on the line For j = 1 To UBound(ListofArrays(i)) instead.

Private Sub ComboBox2_Change()
''When S&S Circumstance selection is made

Dim WS As Worksheet

Set WS = ThisWorkbook.Sheets("Administration")

''Arrays where strings are stored
Dim CircsG1 As Variant
Dim CircsG2 As Variant
Dim CircsG3 As Variant
''Array of Array
Dim ListofArrays As Variant

''String to compare against
Dim Circs As String


Dim i As Integer
Dim j As Integer
Dim NoOfArr As Integer

NoOfArr = 3

CircsG1 = WS.Range("P2:P19").Value
CircsG2 = WS.Range("Q2:Q7").Value
CircsG3 = WS.Range("R2:R5").Value
ListofArrays = Array(CircsG1, CircsG2, CircsG3)

Circs = Me.ComboBox2.Value

For i = 1 To NoOfArr
    For j = 1 To UBound(ListofArrays(i))
        If InStr(ListofArrays(i, j), Circs) Then
            MsgBox ListofArrays(i)
''and some other code
        Else
        Exit Sub
        End If
    Next
Next

Skip:
End Sub

Having investigated my code using various other answers on SO, there are a couple of things I have identified.

The UBound(ListofArrays(i)) is returning a value that is less than the number of strings stored in the array it is looking at. i.e. the range I defined means this value should be 17 but it returning a value of 6.
Runtime error 9 ,Subscript error on line If InStr(ListofArrays(i, j), Circs) Then

K.Dᴀᴠɪs · Accepted Answer · 2019-07-09 23:53:30Z

2

You're dealing with two different bases here. Your ListofArrays has a base of 0, assuming you didn't modify any of your module's base options, but your other arrays have a base of 1 since you pulled the data from the worksheet.

Now we've establish that, you have a syntax issue when indexing your array inside your array.

ListofArrays is only 1-Dimensional, but you are using it like a 2-D array:

InStr(ListofArrays(i, j), Circs)

Also, when you pull data from the worksheet and place into an array as you've done, you're actually creating a 2-D array (since your worksheet is 2 dimensional). You are only pulling a single column of data from this array, so you can just use a (j, 1) for the 2nd dimension.

Here's the proper way to access an array within an array:

Dim i as long, j as long

For i = 0 to Ubound(ListofArrays)
    for j = 1 to Ubound(ListofArrays(i))

        If InStr(ListofArrays(i)(j, 1), Circs) Then MsgBox ListofArrays(i)(j, 1)

    next j
next i

And to avoid the "base issue" altogether, just use LBound():

Dim i as long, j as long

For i = lbound(ListofArrays) to Ubound(ListofArrays)
    for j = LBound(ListofArrays(i)) to Ubound(ListofArrays(i))

        If InStr(ListofArrays(i)(j, 1), Circs) Then MsgBox ListofArrays(i)(j, 1)

    next j
next i

edited Jul 9, 2019 at 23:53

answered Jul 9, 2019 at 23:18

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

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

4 Comments

Dan Sutton Over a year ago

Hi, Thanks for the quick response, I am getting a "next without for" error when using the second option "avoiding Base issue"? It is also not a fan of the additional bracket in If InStr(ListofArrays(i)(j)')', Circs) Then MsgBox ListofArrays(i)(j)

K.Dᴀᴠɪs Over a year ago

I placed my MsgBox ListofArrays(i)(j) (which now I see should be ListofArrays(i)(j, 1), so give me a moment to update on this as well) on the same line as I did the If InStr() statement. If you put on a different line, you need to use End If.

Dan Sutton Over a year ago

Still doesn't like that extra bracket but took this out and works like a charm. Thank you very much :)

K.Dᴀᴠɪs Over a year ago

Ah yeah that was a typo. That's what happens when I don't test what I write (which I usually do, my apologies). Glad to assist!

AAA · Accepted Answer · 2019-07-09 23:16:41Z

0

Arrays in VBA are indexed beginning with zero [1]. Hence why you are having the errors. For example, it should be:

For i = 0 To NoOfArr-1

You would therefore need to rewrite your loops and indices, or change your array declarations. However, the simplest and easiest solution (with no change to your existing code) is to add the following line at the top of the module:

Option Base 1

This ensures that the arrays are 1-indexed.

answered Jul 9, 2019 at 23:16

AAA

3,6801 gold badge18 silver badges33 bronze badges

Collectives™ on Stack Overflow

How do i loop through and array of an array

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related