Running Total Excel or VBA functionReset Based on Cell value-CodePudding

Hi I have a column of 0's and 1's I want to create a running total of the non 0 values un-till it reaches a cell value of 0. Once it hits zero it should, return an empty cell, reset to 0, and begin again from 1 at the next cell value of 1.

Any help would be appreciated, including what I might want to look at to help.

Editing with current solution: Ive found this solution that works, how would I go about making this a function instead of using this Sub()?

Sub test()
Dim value As Integer

value = 0
For i = 1 To Range("Table2").Rows.Count
   If ThisWorkbook.Worksheets("Sheet1").Range("Table2[Current Col]").Cells(i) = 0 Then
        value = 0
        Range("Table2[New Column]")(i) = ""
   ElseIf ThisWorkbook.Worksheets("Sheet1").Range("Table2[Current Col]").Cells(i) = 1 Then
        value = value   1
        Range("Table2[New Column]")(i) = value
   End If
Next i
End Sub

CodePudding user response：

Incrementing Groups

Use variables to avoid long unreadable lines.

Option Explicit

Sub test()
    
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    Dim srg As Range: Set srg = ws.Range("Table2[Current Col]")
    Dim drg As Range: Set drg = ws.Range("Table2[New Col]")

    Dim sValue As Variant
    Dim dValue As Variant
    Dim iValue As Long
    Dim i As Long
    
    For i = 1 To srg.Cells.Count
        ' Read from source cell into a variable ('sValue').
        sValue = srg.Cells(i).Value
        ' Test and write result to a variable ('dValue').
        If IsNumeric(sValue) Then
            If sValue = 1 Then
                iValue = iValue   1
                dValue = iValue
            End If
        Else
            iValue = 0
            dValue = Empty
        End If
        ' Write from the variable ('dValue') to the destination cell.
        drg.Cells(i).Value = dValue
    Next i
    
End Sub