Formula question??

[Brad Grow]

I need to know if there is a way to create a formula that will allow me to
enter multiple values in a cell at different times and this formula will sum
there values.
Example:
I would enter 10 in cell B3.
An hour later I might enter 25 also in cell B3.
After the second entry I would want it to automaticaly display 35.

Is this possible and how would it be done?

Thanks for your help.

 

[Peo Sjoblom]

At the bottom there is a formula solution turning on iterations

http://www.mcgimpsey.com/excel/accumulator.html

--


Regards,


Peo Sjoblom

 

[Gord Dibben]

And make note of the caveat about having no "audit trail" for error
checking.


Gord Dibben MS Excel MVP

 

[Brad Grow]

OK. This is great- it can be done!

Pretty new at this so what do I do with this code? Copy and paste somewhere?

Thanks!

 

[Peo Sjoblom]

Here you go

http://www.mvps.org/dmcritchie/excel/install.htm

--


Regards,


Peo Sjoblom

 

[Brad Grow]

OK. Finally got back on this little project and got it to work. Now how can
I make this work for more than one cell on a worksheet? I have played with
it a little bit and I can make it work anywhere on the worksheet, but only
one spot. Also is there any potential problems by having it apply to a range
of cells?

Thank you agaun for your time and assistance.

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Static dAccumulator As Double
Const WS_RANGE As String = "A1:A10" 'adjust range to suit.
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
dAccumulator = dAccumulator + .Value
Else
dAccumulator = 0
End If
Application.EnableEvents = False
.Value = dAccumulator
Application.EnableEvents = True
End With
End If
End Sub

Should pose no problem with a larger range.


Gord Dibben MS Excel MVP

 

[Brad Grow]

Really appreciate all the help you guys have given. I'd still be at square
one with this if it wasn't for you.

Gord; I was actually referring to having the original accumulator work in
many cells independently of each other. This code seems to make cells A1
thru A10 Accumulate in each cell but then as you go down the column they are
summing themselves up as well. Does that make sense?

I just need each cell to be an accumulator for itself. My actual range
would be D6 through K36. So basically I need to make the original McGimpsey
accumulator apply for this range.

Thanks again for your help.

 

[Gord Dibben]

Poor testing on my part.

I will try to come up with something that works over a range of cells.

In the meantime hang in.........someone else may post a solution.


Gord

 

[Gord Dibben]

Give this a try

Option Explicit
Dim oldval As Variant
Const WS_RANGE As String = "D6:K36"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
oldval = Target.Value 'store value of selected cell
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = .Value + oldval
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord

 

[Gord Dibben]

Just a note...........

Does not trap for errors created by user entering text string.

If you need that post back.


Gord

 

[Brad Grow]

I'm starting to feel bad because I keep bothering you about this.

If I did this right (which I wouldn't swear to) I copy & pasted this to my
worksheet and couldn't get it to work. Is that what I was supposed to do, or
am I missing something here?

Thanks again.

 

[Gord Dibben]

First of all, what you want to do is fraught with peril.

You have no way of trouble-shooting for incorrect data entry..........no
paper trail to check.

Why do you need the cell accumulator in that range of cells?

Maybe there is something else that could be done?

But.............the two events I posted will be pasted into the worksheet
module...........right-click on sheet tab and "View Code". Paste into that
module.

When you select a cell in the range D6:K36 and that cell has a number value
the value will be stored as "oldval".

Enter a new number in the cell and it will be added to "oldval" in that
cell.

i,e, D6 is 10

Select D6 and enter 23 D6 will become 33


Gord

 

[Brad Grow]

Gord:
Great- I got it to work!
One more question and this will be over. If an error comes up(due to a
mistake on my part) it seems that the event stops working. How do I restart
it?

I understand your concern about having an accumulator of this size. This is
one of those situations where this fits my purpose perfectly. This
application use fairly small numbers and is not that critical- a small
mistake here or there just won't end the world in this case.

Thank you again for your help. Hopefully next time I come up with a little
project like this I'll have a little more VBA knowlege and won't have to
bother you!

 

[Gord Dibben]

The one worksheet_change event has a trap to re-enable events if an error
occurs.

I had remmed out that line when testing and forgot to unrem it.

Try this version of the change_event

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Application.IsNumber(.Value) Then 'checks for text
.Value = .Value + oldval
End If
End With
End If
ws_exit:
oldval = "" 'clears oldval
Application.EnableEvents = True
End Sub

Revised selection_change to account for selection value being text string.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Application.IsNumber(Target.Value) Then
oldval = Target.Value
Else
oldval = ""
End If
End If
End Sub


Gord