Progressive Total

[tedc]

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

 

[RichardS]

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]

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]

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]

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.