sum ????? cell inputs ???

[sj_der]

How can you input numbers into the same cell, say (A10) and get another cell, say (A12) to add them all together ?

eg
enter 5 into A10 A12 displays 5
enter 5 into A10 A12 displays 10
enter 20 into A10 A12 displays 30

Help appreciated

Steve

Lincoln

 

[hiker95]

sj_der,

We can use the Worksheet_Change Event for cell A10.


If we start off with this:

Excel Workbook
	A
10
11
12
13
Sheet1

And enter 5 into cell A10, we get this:

Excel Workbook
	A
10	5
11
12	5
13
Sheet1

And enter 5 into cell A10, we get this:

Excel Workbook
	A
10	5
11
12	10
13
Sheet1

And enter 20 into cell A10, we get this:

Excel Workbook
	A
10	20
11
12	30
13
Sheet1

And delete what is in cell A10, or enter a character that is not a number, we get this:

Excel Workbook
	A
10
11
12	30
13
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 03/20/2011
' http://www.mrexcel.com/forum/showthread.php?t=537352
If Intersect(Target, Range("A10")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
If Not IsNumeric(Target) Then
  Target = ""
  Exit Sub
End If
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Range("A12") = Range("A12") + Range("A10")
  .ScreenUpdating = True
  .EnableEvents = True
End With
End Sub

Then enter numbers in cell A10.

 

[MickG]

Try this:-
Right click sheet Tab select "View Code", VB Window appears.
Paste code into window (At top of window, if there is any other code in Window).
Close VB Window
To run code Add number to "A10"
The code will continually add numbers in "A12" when entered number in "A10", but if you add a "0" then the Total will revert to "0".

Option Explicit
Dim temp As Double
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "A10" Then
temp = temp + Target
Range("A12") = temp
If Target = 0 Then temp = 0
End If
End Sub