Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify VB for a Runnning Total
Good Morning, I'm new to the board and look forward to exchanging ideas with all of you. I found the following script off of a Microsoft tutorial for creating a running total on Excel. My problem with it, is asked after the script, below. Thanks in advance for reading: Code: -------------------- ' The Auto_Open name forces this macro to run every time ' the workbook containing this macro is opened. Sub Auto_Open() ' Every time a cell's value is changed, ' the RunningTotal macro runs. Application.OnEntry = "RunningTotal" End Sub '---------------------------------------------------------- ' This macro runs each time the value of a cell changes. ' It adds the current value of the cell to the value of the ' cell comment. Then it stores the new total in the cell comment. Sub RunningTotal() On Error GoTo errorhandler ' Skip cells that have no comment. With Application.Caller ' Checks to see if the cell is a running total by ' checking to see if the first 4 characters of the cell ' comment are "RT= ". NOTE: there is a space after the equal ' sign. If Left(.Comment.Text, 4) = "RT= " Then ' Change the cell's value to the new value in the cell ' plus the old total stored in the cell comment. RT = .Value + Right(.Comment.Text, Len(.Comment.Text) - 4) .Value = RT ' Store the new total in the cell note. .Comment.Text Text:="RT= " & RT End If End With Exit Sub ' Skip over the errorhandler routine. errorhandler: ' End the procedure if no comment in the cell. Exit Sub End Sub '-------------------------------------------------------------- ' This macro sets up a cell to be a running total cell. Sub SetComment() With ActiveCell ' Set comment to indicate that a running total is present. ' If the ActiveCell is empty, multiplying by 1 will ' return a 0. .AddComment .Comment.Text Text:="RT= " & (ActiveCell * 1) End With End Sub -------------------- This code allows me to add to the running total by entering the numerical value in the cell containing the comment. However, say I have Cell A1 and B1 involved. I want to modify the script in a way that I can enter the value in A1, and have B1 keeping a cumulative running total. Cell A1 is labeled as "currentweek" and cell B1 is labeled as "weektodate" Any help is appreciated and thank you again. Robert -- mytibt ------------------------------------------------------------------------ mytibt's Profile: http://www.excelforum.com/member.php...o&userid=24536 View this thread: http://www.excelforum.com/showthread...hreadid=381271 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
Excel 2002 : Any single button to get sub total and grand total ? | Excel Discussion (Misc queries) | |||
Linking worksheets before/after runnning report | Excel Programming | |||
Linking worksheets after runnning report | Excel Discussion (Misc queries) | |||
Code for runnning macros created in XP in older excel versions | Excel Programming |