Multiple Nested Loops and Arrays, SubScript out of range

Question

Still getting used to handling arrays and loops. Would someone mind pointing out the issue with this?

To go over it, its a simple loops that checks a1:a100 in sheet one and checks it against b1:b100 in each sheet then returns the count of it in Sheet1!c1:c100

thanks in advance

 Private Sub TheLoops()

 Dim SearchArray()
 Dim SheetArray()
 Dim ColumArray()
 Dim ReturnArray()
 Dim ModCount As Long
 SearchArray = Sheet1.Range("a1:a100")
 SheetArray(0) = Sheet1
 SheetArray(1) = Sheet2
 SheetArray(2) = Sheet3
 ColumArray = ActiveSheet.Range("b1:b100")
 ReturnArray = Sheet1.Range("c1:c100")
 ModCount = 0

 For I = LBound(SearchArray) To UBound(SearchArray)
     For L = LBound(ReturnArray) To UBound(ReturnArray)
         For J = LBound(SheetArray) To UBound(SheetArray)
             For K = LBound(ColumArray) To UBound(ColumArray)
                 If SearchArray(I) = ColumArray(K) Then
                     ModCount = ModCount + 1
                 End If
             Next K
         Next J
     ReturnArray(L) = ModCount
     ModCount = 0
     Next L
 Next I

 End Sub

Is this correct?

 SearchArray = Sheet1.Range("a1:a100").Value, LBound(SearchArray, 1)

 For I = SearchArray(I, LBound(SearchArray, 2)) to SearchArray(I, UBound(SearchArray, 2))

 etc etc etc

Bathsheba · Accepted Answer · 2016-02-25 14:07:55Z

2

This problem is down to .Range having a default property called Value that returns a two dimensional Variant. This is incompatible with the type you are assigning that to.

Use Dim SearchArray as Variant, SearchArray = Sheet1.Range("a1:a100").Value, LBound(SearchArray, 1) followed by two dimensional indexing: SearchArray(I, LBound(SearchArray, 2)).

Refactor the other variables in a similar way.

answered Feb 25, 2016 at 14:07

Bathsheba

235k35 gold badges376 silver badges504 bronze badges

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

3 Comments

Doug Coats Over a year ago

Ok maybe im not following.... but essentially what youre saying redeclare variables and then when I announce the loop I have to restructure what it needs to go through? Sorry I just want to make sure I am understanding correctly

Bathsheba Over a year ago

Not entirely, you need to replace you declaration with what I have, along with the assignments, and the LBound and UBound functions.

Doug Coats Over a year ago

I posted and a sample edit to see if I understood you correctly. Is that correct?

Collectives™ on Stack Overflow

Multiple Nested Loops and Arrays, SubScript out of range

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related