Cumulative Total

  • Thread starter Thread starter Paul Black
  • Start date Start date
P

Paul Black

Hi Everyone,

I have three Cells, E21, F21 & G21 in a Sheet named BNT which change
EVERY time F9 is pressed.
What I would like to do is to keep a cumulative total of the three
Cells in F24.

For example, if ...

E21 = 0
F21 = 10
G21 = 20

.... the total of the three Cells would be 30 and would go in Cell F24.
Then F9 is pressed again and could change to ...

E21 = 10
F21 = 30
G21 = 0

.... so the new total of the three Cells would be 40, BUT the cumulative
total in Cell F24 would now change to 70.

Is there a way that every time F9 is pressed it adds Cells E21, F21 &
G21 together and puts it on a Sheet named CUM starting in Cell A1. Then
the next time F9 is pressed it adds Cells E21, F21 & G21 together and
puts it on the Sheet named CUM in Cell A2, then Cell A3 etc. Then I
could sum column A:A in the Sheet named CUM and have the cumulative
total in Cell F24 of the Sheet named BNT.
Or is a better way, to have a Macro attached to a button that does the
above and just updates the value in Cell F24 of the Sheet named BNT.
I do not have the MOREFUNC addin available to me unfortunately.

Many thanks in advance.
All the Best.
Paul
 
Try this: right click on your worksheet containing cells E21:G21, select view
code, and paste this code in the code window that appears. Your formula to
get the cumulative total would be =SUM(Cum!A:A)

Private Sub Worksheet_Calculate()
Dim rngDest As Range
Dim lngTotal As Long

Set rngDest = Sheets("Cum").Range("A1")
lngTotal = Application.Sum(Me.Range("E21:G21"))

If IsEmpty(rngDest) Then
rngDest.Value = lngTotal
Else
With rngDest.Parent
.Cells(.Rows.Count, _
rngDest.Column).End(xlUp)(2, 1).Value = lngTotal
End With
End If

End Sub


If you don't need all of the Intermediate totals, you could shorten it to this

Private Sub Worksheet_Calculate()
With Me.Range("F24")
.Value = .Value + Application.Sum(Me.Range("E21:G21"))
End With
End Sub
 
I would go the Macro route.
Sub CumTTL()

Dim a, b, c, d
a = Range("F21").Value
b = Range("G21").Value
c = Range("H21").Value
d = Range("F24").Value
Sheets("Codes1").Activate
Range("F24").Activate
d = a + b + c + d
Range("F24") = d

End Sub
 
Thanks JMB,

Your ...

Private Sub Worksheet_Calculate()
With Me.Range("F24")
.Value = .Value + Application.Sum(Me.Range("E21:G21"))
End With
End Sub

.... works perfectly ( as long as I have Tools, Options & Calculation
set to manual, otherwise it seems to calculate MANY times and come up
with an answer ).

Is there any way I can get it to do exactly the same thing but using a
button please.

Jim,

For some reason I could not get your code to work.

Thanks to you both in advance.
All the Best.
Paul
 
If the Control Toolbox is not visible, click "View\Toolbars\Control Toolbox"
and click on then Command Button icon. Place the button on the sheet and
double=click it. You will be taken to a sub "Private Sub CommandButton1()"
or something similar.

Place your code there.

Then you can right-click the button and choose "Properties" to edit the
Caption, color etc. I will try to come up with a safeguard to prevent its
being used before changes have been made to the other cells.

Let me know if I need to go further with what we have discussed so far. I
will be checking these posts over the weekend.
 
My apologies, I forgot to disable events

At the beginning of the code put
Application.EnableEvents = False

and at the end put
Application.EnableEvents = True
 
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
With Me.Range("F24")
.Value = .Value + Application.Sum(Me.Range("E21:G21"))
End With
Application.EnableEvents = True
End Sub
 
I am very grateful for your time and effort JMB & Jim.
I have adapted the code to ...

Private Sub Calculate_Cumulative_Total()
With Me.Range("E24")
.Value = .Value + Application.Sum(Me.Range("E21:G21"))
End With
End Sub

.... where Cell E24 is merged with Cells F24 AND G24. Unfortunately I am
now getting wrong answers ( Tools, Options & Calculation set to
Automatic ).

When I include ...

Application.EnableEvents = False
Application.EnableEvents = True

.... this produces the wrong answer(s).

Any further help would be greatly appreciated.
Many thanks in advance.
All the Best.
Paul
 
You could put it in a standard code module and assign it to a button. As an
event handler (my previous posts), the code will run whenever the event
occurs (I picked the Worksheet_Calculate event because you said you were
using F9 to calculate), but there are other events that could be used (such
as Worksheet_Change). Event handlers don't require the user to do anything.
As a regular macro that could be assigned to a button and the code would be:

Sub Cumulative()
With Me.Range("F24")
.Value = .Value + Application.Sum(Me.Range("E21:G21"))
End With
End Sub

David McRitchie has some tutorials you might want to peruse that will help
navigate the VBA editor.
http://web.archive.org/web/20031204...cid=/support/excel/content/vba101/default.asp
 
Thanks JMB,

I used your code in a standard module and attached it to a button. I
get the ERROR, "Invalid use of Me keyword".

All the Best.
Paul
 
If used as an event handler, you cannot change the name.
Private Sub Calculate_Cumulative_Total won't work properly.

When you're in the VBA editor you'll see two drop boxes at the top. Left
one should say Worksheet, right one s/b Calculate. If you click on the drop
arrow of the box on the right, you'll see a list of the events you could use
for your purpose. The only other one that I think would be viable would be
"Change". If you select Change, VBA will automatically put

Private Sub Worksheet_Change()

End Sub

in the code window. You don't have any say in what to label these, but you
can pick which event you want to trigger your code and paste the guts of the
macro in between whatever Private Sub / End Sub VBA comes up with when you
select your event.

In the end, it is up to you whether you want the total to compute
automatically or require the user to initiate the calculation (by hitting a
button). Both have pros and cons.

I included a link in last post so that you can become more familiar w/VBA
environment. For what you are asking, I've never seen a solution that did
not involve a macro.
 
Sorry -it's been a long and tiring week.

Sub Cumulative()
With Sheets("BNT").Range("F24")
.Value = .Value + Application.Sum(Sheets("BNT").Range("E21:G21"))
End With
End Sub
 
Back
Top