Question for Gord D

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I read your post from october noted below regarding how to create a two cell
accumulator. I went to the link you referenced and got the code which i
pasted below. Using my visual editor in excel I placed this code in to the
open workbook. When i go to the sheet and change the value in K1, nothing
happesn... What might I be missing. Marcos are enabled so i'm at a loss.

Dave
************
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "j1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("k1").Value = Range("k1").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub
********************

***************
You can do it but what will you do when you make a mistake in entry?

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


Gord Dibben MS Excel MVP

I am wanting to utilize essentially an adding machine function in an Excel
spreadsheet. I can't, presently, find a way to input a number in a cell, have
it included in a total, and then enter another number to be added to that
recently increased sum (just like an adding machine).
Click to expand...
**************
 
Hi,

Did you place the code in the sheet object or a standard code module.

If you right click the sheet tab and pick View Code. Now paste you code
and it should work.

And you may want to change the J reference to upper case.

If .Address(False, False) = "J1" Then

Cheers
Andy
 
Just to add to Andy's post...

..Address(false,false) will return an uppercase column letter. Since you used
lowercase, your If statement will never be true (unless you do something
special).

I like this syntax better:

with target
if intersect(.cells, .range("K1")) is nothing then
'skip it
else
If IsNumeric(.Value) Then
Application.EnableEvents = False
....


And I don't have to be as careful with my upper/lower case typing.
 
Thanks Andy, it worked. I didn't know their was a difference between the
sheet obj and code mod. I still don't understand how the two are different
but it work.

On to the next problem I'm sure I'll be back with other questions on my next
steps.

thx again
dave
 
ok i got this to work now here's a twist that seems to be not allowing this
to work (you guys must luv idiots like me...lol)...

Anyway, if i enter the data manually into J1 it works...however J1 is
actually getting it's value from another cell A1 which is a streamed data
value from another application that is linked to this workbook via DDE. So
when the value in A1 is updated (every second or so), J1 changes to equal
this value and i want K1 to accumulated the running values in J1. It seems i
need to get just the "value" from A1 and paste it into J1 so it's a stand
alone number vs being connected to A1 at all times. Just my thought.

DaveM
 
The link I posted was to John McGimpsey's site.

You have altered John's code and prpbably placed it in the wrong module.

See other responses in this thread.


Gord Dibben MS Excel MVP
 
Having no paper trail is a bit dangerous in my opinion.

I prefer something like this which leaves a paper trail.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'accumulator/summer
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$J$1" And Target.Value <> "" Then
ActiveSheet.Cells(Rows.Count, "K").End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
stoppit:
Application.EnableEvents = True
End Sub

Enter in K1 =SUM(K2:K1000)

Start pounding numbers into J1

If you make a mistake, delete the last number in column K and reenter in J1


Gord Dibben MS Excel MVP
 
Back
Top