# Progressive Total

#### tedc

##### Board Regular
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### RichardS

##### Well-known Member
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
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
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
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
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.

Replies
24
Views
468
Replies
7
Views
178
Replies
0
Views
123
Replies
1
Views
131
Replies
1
Views
88

1,186,909
Messages
5,960,536
Members
438,483
Latest member
Shahin Jack

### 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.

### Which adblocker are you using?

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

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