#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default Question for Gord D

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

On Sun, 21 Oct 2007 16:58:01 -0700, preston-ahp
wrote:

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

**************



  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,489
Default Question for Gord D

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

DaveM wrote:
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

On Sun, 21 Oct 2007 16:58:01 -0700, preston-ahp
wrote:


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


**************



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Question for Gord D

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.

DaveM wrote:

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

On Sun, 21 Oct 2007 16:58:01 -0700, preston-ahp
wrote:

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

**************



--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default Question for Gord D

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

"Andy Pope" wrote:

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

DaveM wrote:
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

On Sun, 21 Oct 2007 16:58:01 -0700, preston-ahp
wrote:


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


**************




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default Question for Gord D

nice....man you guys/gals are good at this... :) Thanks Dave

"Dave Peterson" wrote:

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.

DaveM wrote:

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

On Sun, 21 Oct 2007 16:58:01 -0700, preston-ahp
wrote:

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

**************



--

Dave Peterson



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default Question for Gord D

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

"DaveM" wrote:

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

On Sun, 21 Oct 2007 16:58:01 -0700, preston-ahp
wrote:

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

**************



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Question for Gord D

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

On Sat, 17 Nov 2007 08:26:00 -0800, DaveM
wrote:

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

On Sun, 21 Oct 2007 16:58:01 -0700, preston-ahp
wrote:

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

**************




  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Question for Gord D

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

On Sat, 17 Nov 2007 10:03:40 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Sat, 17 Nov 2007 08:26:00 -0800, DaveM
wrote:

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

On Sun, 21 Oct 2007 16:58:01 -0700, preston-ahp
wrote:

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

**************




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Question SiliconAlleyDude New Users to Excel 2 November 2nd 06 01:56 PM
Question odditie New Users to Excel 6 August 4th 06 06:55 AM
Newbie Question - Subtraction Formula Question [email protected] Excel Discussion (Misc queries) 3 May 5th 06 05:50 PM
The question is an excel question that I need to figure out howto do in excel. Terry Excel Worksheet Functions 3 January 23rd 06 06:22 PM
A question for Gord Dibben Oliver Ferns via OfficeKB.com Excel Discussion (Misc queries) 1 March 4th 05 05:12 PM


All times are GMT +1. The time now is 11:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"