Running Total

[Agzil]

Hello All,

I'm still very new to vba, pardon my misgivings!

I was wondering how to modify this code if I wanted to add a running total of column A on column B rows every time I add a value on column A.
P.S. I'm tucking this to a user-form button.

	A	B	C
1	Coin	Total	Demand
2	2	2	3
3	2	4
4	1	5
5	3	8
6	1	9
7	2	11
8	2	13

<tbody>
</tbody>

Sub Test()
Dim Coin As Long
Dim Total As Long
Dim Demand As Long


Demand = Cells(2, 3)


Total = 0


For Coin = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    
    Total = Total + Cells(Coin, 1)
    If Total > Demand Then
        Cells(Coin, 1).Interior.ColorIndex = 6
        Total = 0
    Else
        Cells(Coin, 1).Interior.ColorIndex = xlNone
    End If
Next Coin
End Sub

I appreciate all the help!
Thank you

 

[dchaney]

I am sure there are other ways to accomplish this, but I am still learning the more in depth areas of VBA, but you can do the following and it should work (I also added this to the "Worksheet_SelectionChange" so anytime someone changes something it runs automatically").

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim inTotal As Integer, inCoin As Integer
Dim Coin As Long, Total As Long, Demand As Long
Dim TotallastRow As Long, CoinlastRow As Long

TotallastRow = Range("B" & Rows.Count).End(xlUp).Row
CoinlastRow = Range("A" & Rows.Count).End(xlUp).Row

inTotal = Cells(TotallastRow, 2).Value
inCoin = Cells(CoinlastRow, 1).Value

If TotallastRow < CoinlastRow Then
    Cells(TotallastRow + 1, 2) = inCoin + inTotal
End If

Demand = Cells(2, 3)


Total = 0


For Coin = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    
    Total = Total + Cells(Coin, 1)
    If Total > Demand Then
        Cells(Coin, 1).Interior.ColorIndex = 6
        Total = 0
    Else
        Cells(Coin, 1).Interior.ColorIndex = xlNone
    End If
Next Coin

End Sub

If you want it to run only when you request it then just change the Sub from

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

to

Sub Test()

 

[Agzil]

I am sure there are other ways to accomplish this, but I am still learning the more in depth areas of VBA, but you can do the following and it should work (I also added this to the "Worksheet_SelectionChange" so anytime someone changes something it runs automatically").

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim inTotal As Integer, inCoin As Integer
Dim Coin As Long, Total As Long, Demand As Long
Dim TotallastRow As Long, CoinlastRow As Long

TotallastRow = Range("B" & Rows.Count).End(xlUp).Row
CoinlastRow = Range("A" & Rows.Count).End(xlUp).Row

inTotal = Cells(TotallastRow, 2).Value
inCoin = Cells(CoinlastRow, 1).Value

If TotallastRow < CoinlastRow Then
    Cells(TotallastRow + 1, 2) = inCoin + inTotal
End If

Demand = Cells(2, 3)


Total = 0


For Coin = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    
    Total = Total + Cells(Coin, 1)
    If Total > Demand Then
        Cells(Coin, 1).Interior.ColorIndex = 6
        Total = 0
    Else
        Cells(Coin, 1).Interior.ColorIndex = xlNone
    End If
Next Coin

End Sub

If you want it to run only when you request it then just change the Sub from

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

to

Sub Test()

Thank you so much for your response dchaney!

I'm sorry, it seems to be just just adding 1 from the previous value.

Also I forgot to mention. What if B2 have already been set to a specific value, so I only want it to start adding B2+A3 and so on?

 

[Dan Waterloo]

Oh! I see.