确定最小值，下标超出范围误差

时间：2021-07-04 16:40:13

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.

第一次海报，业余VBA-ist。我以前在Matlab中有过代码逻辑的经验，但是我发现VBA已经超出了我的能力范围。下面是我用来确定具有多个峰值的特定数据集的局部最小值和最大值的代码。不幸的是，数据是“嘈杂的”;也就是说，当我们跟踪真极值之间的趋势时，由于仪表读数的波动，存在多个局部极大值/极小值。我编辑了一些我在别处找到的代码，我认为它可以确定这些真正的最小点(我可以通过流何时开始/停止来确定它们)，因为最大值似乎从来都不是问题。但当它到达星形线(lngCnt = lngCnt + 1的第二轮)时，会出现“运行时间错误9:下标超出范围”。我试着研究这个问题，但是我不明白要用什么来解决这个问题，也不知道这个答案是如何应用到我的代码中的。

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:

样本数据集(在大约55分钟内，椭圆表示趋势的延续)如下:

0    3000  
0    2900  
0    2850  
0    2825  
0    2800  
24   2800  
23   2775   
21   2775  
19   2750  
170  3400  
245  3600  
290  3800  
290  4000  
290  4200  
...  
305  11600  
175  11800  
23   11700  
19   11600  
20   11500  
0    11400  
0    11300  
0    11200  
0    11100

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.

另外，谁能提供一种更好的方法来将输出数组扩展到只需要的单元格中呢?我尝试过使用lngCnt值，但它不起作用。以前，代码使用数组值代替100和150。

Thank you ahead of time for your help!

提前感谢您的帮助!

1 个解决方案

#1

1  

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

#1

1  

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
标签：

相关文章