Progressive Total

tedc

Board Regular
Joined
Oct 31, 2003
Messages
194
I am looking for code that continues to total, in the TOTAL cell, when numbers are changed in the totalling cells.
The factors that govern the change are a new entry from a combo box.
It would be nice to be able to clear the summing cells on this change.
ie.
Supposing cells C3,C4,C5, = 2,2,2 =6 in say C10
a new entry is added to cell A4 from a combo box
then cells C3,C4,C5, clear
new entries are added to C3,C4,C5= 3,3,3 (which would =9)
this figure is added to the previous total, in this case 6
new total would =6+9= 15 in C10
and so on until a command button is activated.

Ted
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
If you are entering data in C3, C4 and C5, and the total is held in A4, when you click on the command button, you would want the value of A4 added to whatever is in C10, yes? Try recording the actions;

Select A4
Copy
Select C10
Edit|Paset Special|Values

and see what the Macro recorder produces.

Richard
 

tedc

Board Regular
Joined
Oct 31, 2003
Messages
194
Not quite Richard.
The Total is held in C10

The Combo Box is A4, but selecting this action would clear C3:C5 but still keep Total(C10) in memory
More numbers are entered into C:3:C5 and then these are added to the total that is memory then this new total displayed in C10

and so on
 

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
Sorry, I meant Paste Special|Values + Add when selecting C10. You could use a spare cell, and as part of your macro, sum the value of C3:C5, then Copy, Select C10, Paste Special|Values & Add (use both radio buttons)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/01/2004 by Me
'

'
Range("J8").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C[-7]:R[-3]C[-7])"
Selection.Copy
Range("C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlAdd, SkipBlanks:= _
False, Transpose:=False
Range("J8").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select

End Sub

This is really ugly, using R1C1 references, but does it do what you want? (There are VB'ers here who will make C10.value =C10.value + value of C3:C5, but I know nothing.

Richard
 

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
Somrhting like this perhaps:

Sub ChangeTotal()
Range("C10") = "=SUM(C3:C5)+" & CDbl(Range("C10").Value)
Range("C3:C5").ClearContents
End Sub

Assign this macro to the Combox.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,095
Messages
5,768,053
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top