CODEWITHSUNDEEP
excelvba
Dan Sutton
Dan Sutton

Reputation: 63

How do i loop through and array of an array

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.

  1. 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.

  2. Runtime error 9 ,Subscript error on line If InStr(ListofArrays(i, j), Circs) Then

Upvotes: 2

Views: 1068

Answers (2)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

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

Upvotes: 2

AAA
AAA

Reputation: 3670

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.

Upvotes: 0

Related Questions