VBA/Excel: "Subscript out of range" error

Question

I am trying to populate the Hrs array with values conditioned on whether specific cells are empty or not:

Sub Add_training_hrs()
    Dim Rng1, Rng2 As Range
    Dim m, n As Integer
    Dim Hrs() As Double

    Set Rng11 = Application.InputBox("Upper-left cell (e.g., C4): ", "Box #1", Type:=8)
    Set Rng22 = Application.InputBox("Lower-right cell: ", "Box #2", Type:=8)

    Col11 = Rng11.Column
    Row11 = Rng11.Row
    Col22 = Rng22.Column
    Row22 = Rng22.Row

    nRows = Row22 - Row11
    nCols = Col22 - Col11

    ReDim Hrs(1 To nCols)

    Debug.Print "Hrs(1) = "; Hrs(1)

    For n = 1 To nRows
        For m = 1 To nCols
            If IsEmpty(Cells(n, m).Value) Then
                Hrs(m) = 0
                Debug.Print "Hrs(m) = "; Hrs(m)
            Else
                Hrs(m) = Cells(Row11 - 1, m).Value
            End If
        Cells(n, Col22 + 1).Value = Application.Sum(Hrs)
        Erase Hrs
        Next m
    Next n
End Sub

For some reason, I keep getting the "Subscription out..." error right after my If... Then statement at Hrs(m) = 0, but I can't figure out why. Any advice is appreciated, and if there is a duplicate question out there that can help, don't hesitate in letting me know (I've looked...).

Updated (and working) code

There were various problems with my original script, but I got it to work. I'm posting it here in case it helps others:

Sub Add_training_hrs()
    Dim Rng11 As Range
    Dim Rng22 As Range
    Dim m As Integer
    Dim n As Integer
    Dim Hrs() As Double

    Set Rng11 = Application.InputBox("Upper-left cell (e.g., C4): ", "Box #1", Type:=8)
    Set Rng22 = Application.InputBox("Lower-right cell: ", "Box #2", Type:=8)

    Col11 = Rng11.Column
    Row11 = Rng11.Row
    Col22 = Rng22.Column
    Row22 = Rng22.Row

    nRows = (Row22 - Row11) + 1
    nCols = (Col22 - Col11) + 1

    ReDim Hrs(1 To nCols)

    For n = 0 To nRows - 1
        For m = 0 To nCols - 1
            If IsEmpty(Cells(Row11 + n, Col11 + m).Value) Then
                Hrs(m + 1) = 0
            Else
                Hrs(m + 1) = Cells(Row11 - 1, Col11 + m).Value
            End If
        Next m
        Cells(Row11 + n, Col22 + 1).Value = Application.Sum(Hrs)
        Erase Hrs
        ReDim Hrs(1 To nCols)
    Next n
End Sub

Maybe a typo, but you define Dim Rng1, Rng2 As Range and use Rng11 and Rng22. BTW, in Dim Rng1, Rng2 As Range, only Rng2 is of type Range, Rng1 is a Variant. In VBA you need to be explicit with each variable, even on the same line. (same with Dim m, n As Integer) — Vincent G
If you are only intending to do a Sum with the array, is there any reason you don't want to add one or more zeroes? It seems like 5+4+3 will end up with the same answer as 5+0+0+4+0+3+0, so I think you might be over-complicating your code for no reason. — YowE3K
@VincentG - thanks for pointing out these obvious problems. This is what happens to me when I'm writing code at 1 AM... — Verde

Vincent G Vincent G · Accepted Answer · 2017-11-17T08:20:10

I suppose you get the error only starting on the second row.

When you Erase a dynamic array, you are releasing the memory (see Erase )

You will need to reallocate it after.

VBA/Excel: "Subscript out of range" error

Updated (and working) code

1 Answers