Excel VBA Nested For Loop Returns Run Time Error 9

Question

After the 12th found match of the loop, I have a Run-time Error 9 on the rptVals array. Basically, the rptcount hits 247 - where rptRows has a count of 246. I have tried doubling and quadrupling the size of rptRows, and each time I pass the 12th match I get the error. I tried loading a different data set that has one more row of data than the first workbook, and I get the error there after the 13th match - again regardless of rptRows count size, but always matching report counts maximum count.

Any ideas why this is happening? Also, I'm a chemist, not a programmer, so sorry if this isn't the prettiest code or the most efficient or whatever. If it works, I'm happy. Also, I've been made aware of Dictionaries, but I have a better grasp on arrays and loops than I do dictionaries (which obviosly isn't saying much, but oh well).

Sub PrntFllEle()

    With Workbooks("Full Element Analysis Current").Worksheets("All _
    Samples")

    rptRows = Range("H6:IS6").Columns.Count  'here is the start of the 
                                             'problem. rptRows = 246
    'rptrng = rptRows * 2  I made this variable to double/quadruple the _ 
                           size of rptRows count
    rptVals = .Range("H6:IS6" & rptRows).Value

    End With

With Workbooks(FlNm).Worksheets("Report")
    'rEleAn, seen below the range of data captured in a separate sub.
    'This will also have an associated array ElAr from the other sub.

    chkRows = rEleAn.Rows.Count

End With

For rptcount = LBound(rptVals) To UBound(rptVals)
    For chkcount = LBound(ElAr) To UBound(ElAr)

        If rptVals(1, rptcount) <> "" Then    'I had to include this as _   
                                               I have some blank cells _   
                                               in array and this was the _                                               
                                               quickest way to deal with it.
        'This next line is where the run-time error occurs. rptVals = _
         Subscript out of Range and rptcount = 247
        'Note, the UBound(rptVals) is 6241.

            If rptVals(1, rptcount) = Mid((ElAr(chkcount, 1)), 1, 2) Then
                MsgBox rptVals(1, rptcount) 
                'MsgBox just a place holder for now.

            End If
        Else
            Exit For
        End If

    Next
Next

End Sub

All variables are global, btw. I've check those values, and everything that could affect this is Long. All arrays are Variants as I have strings and numbers to deal with. Ranges are appropriately Dim As Range.

Hover your mouse over rptVals and ElAr to see which one is giving you the error, I assume it is actually ElAr(chkcount, 1) - can you dump the value of that? It might be the MID function doesn;t like your values — dbmitch
– dbmitch, Commented May 30, 2018 at 1:47
@dbmitch No, I've check that and it's the rptVals that is returning the error. As for the Mid, the String for the worksheet I'm trying to pull data from (FlNm) is "XY " where XY is an chemical element symbol (Fe, Al, Au, etc). I have not idea why there are all the spaces there and I have no control over how the original is made, but I'm using Mid so it just sees the element symbol. — webfootedoldman
– webfootedoldman, Commented May 30, 2018 at 5:13
@dbmitch I just noticed that didn't come out the way I was expecting. the String looks like "XY__________". — webfootedoldman
– webfootedoldman, Commented May 30, 2018 at 6:10

user4039065 · Accepted Answer · 2018-05-30 01:53:28Z

1

Your For ... Next construction is defaulting to the first rank of your array. You want the second rank.

For rptcount = LBound(rptVals, 2) To UBound(rptVals, 2)
    For chkcount = LBound(ElAr, 1) To UBound(ElAr, 1)

answered May 30, 2018 at 1:53

user4039065

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

2 Comments

webfootedoldman Over a year ago

OK, that work. Thank you! Question though, did rptVals need to be rptVals, 2 because I was looping through values on the row by column? And ElAr needed to be ElAr,1 because I was looping through values in the column by row?

user4039065 Over a year ago

Yes, if you are equating the ranks of a 2-D array to the rows and columns on a worksheet, then that is a suitable explanation.

Collectives™ on Stack Overflow

Excel VBA Nested For Loop Returns Run Time Error 9

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related