在VBA中循环单元格并在必要时覆盖它们

Question

我正在尝试检测并处理数据中的“重置”。数据应该一直在增加，但有时传感器喜欢重置到一个更低的值。我想检测这些，并通过将先前的值与当前值相加得到当前值来校正它们

示例：

1
2
5
10
11
100
150
2
3
5

应该是：

1
2
5
10
11
100
150
152
153
155

下面是一个对我有用的Python实现：

def process(arr):
    max_before_reset = 0
    reset_detected = False
    old = arr[:]
    for i, e in enumerate(old): # enumerate contains original array
        if i == 0:
            continue
        if e < old[i-1]:
            print '\t', e, old[i-1]
            max_before_reset = arr[i-1]
            reset_detected = True
        if(reset_detected):
            arr[i] = old[i] + max_before_reset
            print old
    return arr
a = [97, 99, 100, 2, 3, 5, 6, 4, 3];
print process(a)

这需要在VBA中完成，所以我尝试了一下：

Sub ProcessData_test(ByVal RawColumn As String, ByVal ProcessedColumn As String)
    Dim NumRows As Integer
    Dim MaxBeforeReset As Integer
    Dim ResetDetected As Boolean
    Const ps As String = "test2"
    Const rds As String = "test1"

    MaxBeforeReset = 0
    ResetDetected = False
    With Sheets(rds)
        NumRows = .Range(RawColumn & .Rows.Count).End(xlUp).Row
    End With
    'MsgBox NumRows

    For i = 1 To NumRows
        If i = 1 Then
        Else
            If Worksheets(rds).Range(RawColumn & i).Value < Worksheets(rds).Range(RawColumn & i).Value Then
                MaxBeforeReset = Worksheets(ps).Range(ProcessedColumn & (i - 1)).Value
                ResetDetected = True
            End If
            If ResetDetected Then
                Worksheets(ps).Range(ProcessedColumn & i).Value = Worksheets(rds).Range(RawColumn & i).Value + MaxBeforeReset
            End If
        End If
        Next i
End Sub

Sub Test()
    Dim a As String, b As String
    a = "A"
    b = "A"
    Call ProcessData_test(a, b)
End Sub

但出于某种原因，它并没有修改test2表中的单元格。我似乎不明白为什么。有什么想法吗

Answer 1

在@chris neilsen的输入错误之后，再加上this thread中的一些工作，最后的（工作）脚本如下：

Sub ProcessData_test(ByVal RawColumn As String, ByVal ProcessedColumn As String)

    Dim NumRows As Integer
    Dim ResetValue As Integer

    Const ps As String = "test2"
    Const rds As String = "test1"

    With Sheets(rds)
        NumRows = .Range(RawColumn & .Rows.Count).End(xlUp).Row
    End With
    'MsgBox NumRows
    ResetValue = 0
    'First Row
    Worksheets(ps).Range(ProcessedColumn & 1).Value = Worksheets(rds).Range(RawColumn & 1).Value

    'All other rows.
    For i = 2 To NumRows
        If Worksheets(rds).Range(RawColumn & i).Value >= Worksheets(rds).Range(RawColumn & i - 1).Value Then
            Worksheets(ps).Range(ProcessedColumn & i).Value = Worksheets(rds).Range(RawColumn & i).Value + ResetValue
        Else
            ResetValue = Worksheets(ps).Range(ProcessedColumn & i - 1).Value
            Worksheets(ps).Range(ProcessedColumn & i).Value = Worksheets(rds).Range(RawColumn & i).Value + ResetValue
        End If
    Next i

End Sub

Answer 2

简单打字：

您的线路（请注意<；（相同）

If Worksheets(rds).Range(RawColumn & i).Value < _
   Worksheets(rds).Range(RawColumn & i).Value Then

应该是

If Worksheets(rds).Range(RawColumn & i).Value < _
   Worksheets(rds).Range(RawColumn & i - 1).Value Then