Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Question | New Users to Excel | |||
Question | New Users to Excel | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions | |||
A question for Gord Dibben | Excel Discussion (Misc queries) |