Determining minima, subscript out of range error

Question

First time poster here, amateur VBA-ist. I have past code-logic experience in Matlab, but I've found VBA is beyond me. Below is the code I'm using to determine local minima and maxima for a certain data set with multiple peaks. Unfortunately the data is "Noisy"; meaning there are multiple local maxima/minima as we follow the trends between the true extrema due to the fluctuation in the meter reading.
I have edited some code I found elsewhere in a way I thought it would determine these true minimum points (I can determine them via when flow starts/stops), as the maxima never seemed a problem. But when it reaches the starred line (second round of lngCnt = lngCnt + 1) there is a "Run time error 9: subscript out of range". I tried to research the problem but I was not able to understand what was being prescribed to fix it, or see how the answer applied to my code.

Here is my code I'm using:

Sub maxmin()
Dim X
Dim Y
Dim lngRow As Long
Dim lngCnt As Long

X = Range([A2], Cells(Rows.Count, "C").End(xlUp)) 'self defining function for the range over which the data will be analyzed, data in spreadsheet must start in L26
Y = Application.Transpose(X) 'creates a column rather than a row


For lngRow = 2 To UBound(X, 1) - 1 'defines the function for the long variable row, to the upper bound of the column
    If X(lngRow, 3) > X(lngRow - 1, 3) Then 'logic statement to assist in max/min
        If X(lngRow, 3) > X(lngRow + 1, 3) Then 'logic statement
            lngCnt = lngCnt + 1
            Y(1, lngCnt) = X(lngRow, 1)
            Y(2, lngCnt) = X(lngRow, 2)
            Y(3, lngCnt) = X(lngRow, 3)
        End If
    Else
        If X(lngRow - 1, 1) < 100 Then 'this and the following line determine where the min is located based off the change in flow rate
            If X(lngRow, 1) > 150 Then
                lngCnt = lngCnt + 1
                Y(1, lngCnt) = X(lngRow, 1)
                Y(2, lngCnt) = X(lngRow, 2)
                Y(3, lngCnt) = X(lngRow, 3)
            End If
        End If
    End If
Next lngRow

ReDim Preserve Y(1 To 3, 1 To lngCnt)

Range("D2:F4300 ") = Application.Transpose(Y) 'prints my data to desired cells
End Sub

Sample set of data (over a period of approximately 55 minutes, where the ellipses represent a continuation of that trend) would read:

What about the indexing with that line gives me a subscript error?

Also, can anyone offer a better way to scale my output array to only the cells this needs? I've tried using the lngCnt value but it does not work. Previously the code worked by using array values in place of the 100 and 150.

Thank you ahead of time for your help!

The starred line should never produce a "subscript out of range" error - are you sure that's the problem line? — Tim Williams
Also: your X has dimensions (#rows, 3) so after the transpose Y will be (3, #rows). You can't use Redim Preserve to adjust both dimensions: it only works on the last dimension of any multi-dimensional array. — Tim Williams
Thank you for your reply! it turns out the redim statement was throwing the subscript error, despite the debugging showing the lngCnt (which didn't make sense to me). @TimWilliams Regarding the second question, is there an easy way to accomplish that? — Josh M.
I have updated the code above with what is working now, I forgot to add a row for the new array. Also, it seems my logic for the minimum is not working (the part after the else) is there a logical fallacy I'm missing here? — Josh M.

Tim Williams Tim Williams · Accepted Answer · 2016-05-25T23:12:08

Range("D2:F4300") = Application.Transpose(Y)

could be

Range("D2").resize(Ubound(Y,2), Ubound(Y,1)).Value = Application.Transpose(Y)

Without the transpose you would flip the bounds around:

Range("D2").resize(Ubound(Y,1), Ubound(Y,2)).Value = Y

Determining minima, subscript out of range error

1 Answers