Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total
Hi I have a value in cell A1 (Currency) and I want to be able to enter value in B1 and have that added to the value in A1. Simple but I wan the new value in A1 to remain even when I delete the value in B1 an for A1 to be updated every time I enter a value in B1 Please advise an easy way to do this. Thanks V -- uplink60 ----------------------------------------------------------------------- uplink600's Profile: http://www.excelforum.com/member.php...nfo&userid=940 View this thread: http://www.excelforum.com/showthread.php?threadid=39052 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total
right click on the sheet tab and select view code. Paste in code like this:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then If Not IsEmpty(Target) Then If IsNumeric(Target) Then Range("A1").Value = Range("A1").Value + _ Range("B1").Value End If End If End If End Sub -- Regards, Tom Ogilvy "uplink600" wrote in message ... Hi I have a value in cell A1 (Currency) and I want to be able to enter a value in B1 and have that added to the value in A1. Simple but I want the new value in A1 to remain even when I delete the value in B1 and for A1 to be updated every time I enter a value in B1 Please advise an easy way to do this. Thanks VC -- uplink600 ------------------------------------------------------------------------ uplink600's Profile: http://www.excelforum.com/member.php...fo&userid=9408 View this thread: http://www.excelforum.com/showthread...hreadid=390521 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total
VC,
Copy this code, right-click on the sheet tab, select view code, and paste in the window that appears. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$B$1" Then Exit Sub Application.EnableEvents = False Range("A1").Value = Range("A1").Value + Range("B1").Value Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "uplink600" wrote in message ... Hi I have a value in cell A1 (Currency) and I want to be able to enter a value in B1 and have that added to the value in A1. Simple but I want the new value in A1 to remain even when I delete the value in B1 and for A1 to be updated every time I enter a value in B1 Please advise an easy way to do this. Thanks VC -- uplink600 ------------------------------------------------------------------------ uplink600's Profile: http://www.excelforum.com/member.php...fo&userid=9408 View this thread: http://www.excelforum.com/showthread...hreadid=390521 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total
This would go in the code module for the worksheet (in VBA editor, Project
Explorer, double-click on the line that shows the worksheet you want this to work on) Private Sub Worksheet_Change(ByVal Target As Range) If Not (Intersect(Target, Range("B1")) Is Nothing) Then _ Range("A1").Value = Range("A1").Value + Range("B1").Value End Sub But note a weakness in this way of doing things: if you make a mistake in the value you enter in B1 there is no going back - no record of what values make up your running total. It is easy to make errors this way that are untraceable. -- - K Dales "uplink600" wrote: Hi I have a value in cell A1 (Currency) and I want to be able to enter a value in B1 and have that added to the value in A1. Simple but I want the new value in A1 to remain even when I delete the value in B1 and for A1 to be updated every time I enter a value in B1 Please advise an easy way to do this. Thanks VC -- uplink600 ------------------------------------------------------------------------ uplink600's Profile: http://www.excelforum.com/member.php...fo&userid=9408 View this thread: http://www.excelforum.com/showthread...hreadid=390521 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total
Thanks Guys that works fine Can you advise some code to use if I want to use a loop to work throug rows 1 to 20 to do the same task. Thank -- uplink60 ----------------------------------------------------------------------- uplink600's Profile: http://www.excelforum.com/member.php...nfo&userid=940 View this thread: http://www.excelforum.com/showthread.php?threadid=39052 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B20")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False Target(1, 0).Value = Target(1, 0).Value + Target.Value Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "uplink600" wrote in message ... Thanks Guys that works fine Can you advise some code to use if I want to use a loop to work through rows 1 to 20 to do the same task. Thanks -- uplink600 ------------------------------------------------------------------------ uplink600's Profile: http://www.excelforum.com/member.php...fo&userid=9408 View this thread: http://www.excelforum.com/showthread...hreadid=390521 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total
Just to add:
If a non numeric value is inadvertently entered in column B, this can throw an error and leave you with events disabled - wondering what happened. It is useful to insert a check to make sure the value to be added is numeric: Private Sub Worksheet_Change(ByVal Target As Range) On Error goto ErrHandler If Intersect(Target, Range("B1:B20")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False if isnumeric(target.value) then Target(1, 0).Value = Target(1, 0).Value + Target.Value end if ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B1:B20")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False Target(1, 0).Value = Target(1, 0).Value + Target.Value Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "uplink600" wrote in message ... Thanks Guys that works fine Can you advise some code to use if I want to use a loop to work through rows 1 to 20 to do the same task. Thanks -- uplink600 ------------------------------------------------------------------------ uplink600's Profile: http://www.excelforum.com/member.php...fo&userid=9408 View this thread: http://www.excelforum.com/showthread...hreadid=390521 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total
Message for Bernie Thanks for that Just one more thing that I forgot. I need this to work on variou ranges such as B1:B20, B24:B34, B38:B48. Can the code be ammended to work on more than one range of cells. Thank -- uplink60 ----------------------------------------------------------------------- uplink600's Profile: http://www.excelforum.com/member.php...nfo&userid=940 View this thread: http://www.excelforum.com/showthread.php?threadid=39052 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Total
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B20, B24:B34, B38:B48")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False Target(1, 0).Value = Target(1, 0).Value + Target.Value Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "uplink600" wrote in message ... Message for Bernie Thanks for that Just one more thing that I forgot. I need this to work on various ranges such as B1:B20, B24:B34, B38:B48. Can the code be ammended to work on more than one range of cells. Thanks -- uplink600 ------------------------------------------------------------------------ uplink600's Profile: http://www.excelforum.com/member.php...fo&userid=9408 View this thread: http://www.excelforum.com/showthread...hreadid=390521 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
running total | Excel Worksheet Functions | |||
running total and average of that total after 3 events | Excel Discussion (Misc queries) | |||
running total and average of that total after 3 events | Excel Discussion (Misc queries) | |||
Running total.... | Excel Discussion (Misc queries) |