Cumulative Total

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 *** 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 *** in Cell A2, then Cell A3 etc. Then I
could sum column A:A in the Sheet named *** 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(***!A:A)

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

Set rngDest = Sheets("***").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

"Paul Black" wrote:

> 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 *** 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 *** in Cell A2, then Cell A3 etc. Then I
> could sum column A:A in the Sheet named *** 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
>
>

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
--
Best wishes,

Jim


"Paul Black" wrote:

> 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 *** 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 *** in Cell A2, then Cell A3 etc. Then I
> could sum column A:A in the Sheet named *** 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
>
>

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

Jim Jackson wrote:
> 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
> --
> Best wishes,
>
> Jim
>
>
> "Paul Black" wrote:
>
> > 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 *** 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 *** in Cell A2, then Cell A3 etc. Then I
> > could sum column A:A in the Sheet named *** 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
> >
> >

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.

--
Best wishes,

Jim


"Paul Black" wrote:

> 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
>
> Jim Jackson wrote:
> > 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
> > --
> > Best wishes,
> >
> > Jim
> >
> >
> > "Paul Black" wrote:
> >
> > > 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 *** 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 *** in Cell A2, then Cell A3 etc. Then I
> > > could sum column A:A in the Sheet named *** 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
> > >
> > >
>
>

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


"Paul Black" wrote:

> 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
>
> Jim Jackson wrote:
> > 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
> > --
> > Best wishes,
> >
> > Jim
> >
> >
> > "Paul Black" wrote:
> >
> > > 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 *** 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 *** in Cell A2, then Cell A3 etc. Then I
> > > could sum column A:A in the Sheet named *** 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
> > >
> > >
>
>

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


"Paul Black" wrote:

> 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
>
> Jim Jackson wrote:
> > 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
> > --
> > Best wishes,
> >
> > Jim
> >
> >
> > "Paul Black" wrote:
> >
> > > 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 *** 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 *** in Cell A2, then Cell A3 etc. Then I
> > > could sum column A:A in the Sheet named *** 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
> > >
> > >
>
>

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

JMB wrote:
> 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
>
>
> "Paul Black" wrote:
>
> > 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
> >
> > Jim Jackson wrote:
> > > 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
> > > --
> > > Best wishes,
> > >
> > > Jim
> > >
> > >
> > > "Paul Black" wrote:
> > >
> > > > 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 *** 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 *** in Cell A2, then Cell A3 etc. Then I
> > > > could sum column A:A in the Sheet named *** 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
> > > >
> > > >
> >
> >

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/200312040...01/default.asp

"Paul Black" wrote:

> 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
>
> Jim Jackson wrote:
> > 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
> > --
> > Best wishes,
> >
> > Jim
> >
> >
> > "Paul Black" wrote:
> >
> > > 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 *** 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 *** in Cell A2, then Cell A3 etc. Then I
> > > could sum column A:A in the Sheet named *** 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
> > >
> > >
>
>

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

JMB wrote:
> 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/200312040...01/default.asp
>
> "Paul Black" wrote:
>
> > 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
> >
> > Jim Jackson wrote:
> > > 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
> > > --
> > > Best wishes,
> > >
> > > Jim
> > >
> > >
> > > "Paul Black" wrote:
> > >
> > > > 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 *** 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 *** in Cell A2, then Cell A3 etc. Then I
> > > > could sum column A:A in the Sheet named *** 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
> > > >
> > > >
> >
> >

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.


"Paul Black" wrote:

> 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
>
> JMB wrote:
> > 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
> >
> >
> > "Paul Black" wrote:
> >
> > > 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
> > >
> > > Jim Jackson wrote:
> > > > 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
> > > > --
> > > > Best wishes,
> > > >
> > > > Jim
> > > >
> > > >
> > > > "Paul Black" wrote:
> > > >
> > > > > 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 *** 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 *** in Cell A2, then Cell A3 etc. Then I
> > > > > could sum column A:A in the Sheet named *** 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
> > > > >
> > > > >
> > >
> > >
>
>

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


"Paul Black" wrote:

> 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
>
> JMB wrote:
> > 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/200312040...01/default.asp
> >
> > "Paul Black" wrote:
> >
> > > 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
> > >
> > > Jim Jackson wrote:
> > > > 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
> > > > --
> > > > Best wishes,
> > > >
> > > > Jim
> > > >
> > > >
> > > > "Paul Black" wrote:
> > > >
> > > > > 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 *** 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 *** in Cell A2, then Cell A3 etc. Then I
> > > > > could sum column A:A in the Sheet named *** 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
> > > > >
> > > > >
> > >
> > >
>
>