addition of new data to value already in a cell

[Pauline]

Click in the cell with the value 3. You will see the 3 appear in the
address bar at the top, type the add sign and then 4. Press Enter and it
should come up with 7.

Pauline

 

[Lee Frette]

A friend would like to be able to go to a cell that contains the value 3,
type in 4, and have the value 7 appear in the cell. Any thoughts?

In case it isn't obvious, I am not an Excel guru.

Thanks.

 

[Earl Kiosterud]

Pauline,

There are ways to do this, sort of. But consider this: When you key in
values, there's no record of which of your values have been swallowed up by
the cell, and which haven't yet been entered.
You can lose track. So for many options, it's not a good solution. Better
to list the values in a column and total them with a formula.

But if you insist:

Tools - Options - Calculation. Check "Iteration" and set maximum iterations
to 1. For this example, the value will be entered into B2. So B2 will
always indicate the most recent entry. C2 will keep the running total, in
C2: =B2+C2. Now to clear what's in C2, enter it's value negatively into
B2.

Or if you insist on entering the new values directly into the summing cell,
use this event macro in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Static Counter
If Not Intersect(Target, Range("B2")) Is Nothing Then
Counter = Counter + Target
Application.EnableEvents = False ' prevent this from retriggering itself
Target = Counter
Application.EnableEvents = True
End If
End Sub

It's barebones. For example if other routines are also messing with
EnableEvents, it'll have to be changed.

Earl Kiosterud
Mvpearl omitthisword at verizon period net.
--------------------------------------------

 

[Earl Kiosterud]

I didn't mean to respond to Pauline's follow-up, but to Lee's OP. Sorrrry!

 

[Pauline]

Very interesting though, I will try it out Pauline

 

[Otto Moehrbach]

Lee
Given what Earl said regarding history of the cell values, there is a
way to do what you want without retaining a history.
It would take an event macro and a regular macro. The event macro is
the Worksheet_Change macro below. The macro would capture the current value
(the new value just typed into the cell), use the Undo command to return to
the old value, capture it, and add the two and put that in the cell. All of
this would be transparent to the user. The user would just see that the
value just entered is added to the old value.
I'm also sending you, via email, a small file that contains these two macros
in their proper locations. I chose A1:A5 as the range in which the numbers
will add up. Change this as needed. Note that the event macro is tied to a
specific sheet. The feature will not work if you move to another sheet,
without some changes. Post back or contact me direct if you need more.
HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then _
Call AddThem(Range(Target.Address))
End Sub

Sub AddThem(i As Range)
Application.ScreenUpdating = False
Dim ValOld As Single
Dim ValNew As Single
ValNew = i.Value
On Error GoTo ErrHandler
Application.EnableEvents = False
Application.Undo
ValOld = i.Value
i.Value = ValOld + ValNew
ErrHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Otto Moehrbach]

Lee
I tried to send you that file at (e-mail address removed) and it came back
as undeliverable. Post back with a good address if you want me to send it
to you. HTH Otto