Collection subscript error 9 used in a For/If loop

[Ficus88]

Hey guys,

I'm trying to manipulate 2 collections in my code. After comparing the difference between 2 values I need to remove those that are less than a set number. However, I run into a "Run-time error '9': Subscript out of range."

I've pulled out the relevant sections from my main code to test the error. In the main code I create the collections with if statements and for loops examining data in an excel column. This part is working fine as I can consistently debug print the full collections, so I've just added the proper values manually below.

I can get the If statement to work on its own with same subscripts as in the For loop, but as soon as I put it in the For loop it falls apart. Maybe I'm missing something obvious idk. I'm very stuck and would appreciate any help whatsoever! Thanks!!

Sub SeedCompiler()


Dim SeedStarts As New Collection
Dim SeedEnds As New Collection
Dim i As Long
Dim steve As Long


    SeedStarts.Add 10
    SeedStarts.Add 15
    SeedStarts.Add 50
    SeedStarts.Add 77
    SeedStarts.Add 86
    SeedEnds.Add 13
    SeedEnds.Add 25
    SeedEnds.Add 75
    SeedEnds.Add 80
    SeedEnds.Add 90


    Debug.Print "SeedStarts R1"
    For i = 1 To SeedStarts.Count
        Debug.Print SeedStarts(i)
    Next i
    
    Debug.Print "SeedEnds R1"
    For i = 1 To SeedEnds.Count
        Debug.Print SeedEnds(i)
    Next i
    
i = 1
Debug.Print "Math Test should =2"
steve = (SeedStarts(i + 1) - SeedEnds(i))
Debug.Print steve


    For i = 1 To SeedStarts.Count
        If (SeedStarts(i + 1) - SeedEnds(i)) < 5 Then
            SeedStarts.Remove (i + 1)
            SeedEnds.Remove (i)
            i = 1
        Else
        End If
    Next i
    
    
    Debug.Print "SeedStarts R2"
    For i = 1 To SeedStarts.Count
        Debug.Print SeedStarts(i)
    Next i
    
    Debug.Print "SeedEnds R2"
    For i = 1 To SeedEnds.Count
        Debug.Print SeedEnds(i)
    Next i




End Sub

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I have not looked through your code in detail but I suspect that the Remove method is causing the issue. The problem is that if you remove an item then all the index numbers larger than that are decremented by one.

The usual trick is to run the For/Next loops backwards like this, for instance:

For i = SeedStarts.Count To 1 Step -1

If you still have an issue please come back and I will take a more "in depth" look.


regards,

 

[Ficus88]

Perfect! all fixed. Thanks Rick!

 

[RickXL]

Great news. Thanks for letting me know.


Regards,