Excel 2013 VBA - Runtime error 9: Subscript out of range

Question

I'm new to VBA and did run into a problem I can't seem to find a solution to on my own -> so Your help is much appreciated :).

I'm getting "Run-time error 9: Subscript out of range" when attempting to run following code:

Dim i as Long, l as Long, x as Long, y as Long, z as Long, lastrow as Long

   '..some unrelevant code
   x = 2

   For y = 1 To lastrow Step 2: For i = y To lastrow Step 2: For z = 0 To 1

   '..do stuff

   If z = 0 Then

        Dim datarange As Variant
        Dim myvar As Double
        Dim comp As Double
        Dim lrow As Long
        Dim lcol As Long

        l = 0
        lrow = x
        datarange = Range("k" & x & ":" & "ab" & x + 1).Value
        For lcol = 11 To 28
            myvar = datarange(lrow, lcol)
            comp = datarange(lrow + 1, lcol)
            If comp > 1 Then
                If myvar > 1 Then
                l = l + 1
                End If
            Else: l = l + 1
            End If
        Next

   End If

   x = x + 1

   Next: Next: Next

   '..etc

I got to this point after implementing the code-part with grey background in effort to include following knowledge into my project: http://blogs.office.com/2008/10/03/what-is-the-fastest-way-to-scan-a-large-range-in-excel/ (third point "Use a variant type variable").

Thanks in advance!

what line do you get the error on? You aren't declaring datarange correctly if you intend it to be an array you should declare it with Dim datarange() As Variant. — D_Zab
Row and column indexes into datarange both start at 1, and run to #rows_in_datarange and #cols_in_datarange respectively, since it has its own coordinate system which is independent of its position on the worksheet. — Tim Williams
When you set datarange, you do not use the CurrentRegion member of the range object as the sample code you link to does. — collapsar
Thanks for your blazing fast responses guys :)! ..got it to run. Especially thanking Tim for pointing this out.. was wondering about it but dind't find an answer through googling a bit. — Fr4gFr0g

Pillgram Pillgram · Accepted Answer · 2015-02-14T07:28:00

If you'll pardon the expression, you've two-stepped out of the bounds of the array:)

The index numbers of the dataRange array are different then their row/column numbers. If you go to VB Editor, View, and the Locals window as you are stepping through the code (F8), you will see that the array is 1 to 2 by 1 to 18.

Excel 2013 VBA - Runtime error 9: Subscript out of range

1 Answers