Tom’s Tutorials for Excel:Running Sum in Same Cell
Here is a WorksheetChange event in VBA that allows any cell in column A to accept a number you enter, add it to whatever number was already in that same cell, and display the resulting sum. For example, if cell A9 currently holds the number 2 and you enter the number 3 in that cell, the resulting value of cell A9 will be 5.
To implement the code, right-click on your worksheet tab and select View Code. Paste the below code into the large white area that is the worksheet module. To get back to your worksheet, press the Alt+Q keyboard combination. To test the code, enter a number in any cell in column A, then enter another number in that same cell and see the resulting sum. To bring the cell back to a starting zero value, simply select the cell and press the Delete key.
Here is the code, with comments to explain what each line of code does.
Private Sub Worksheet_Change(ByVal Target As Range) 'Only apply this effect to column A (column 1 in VBA-Speak). 'At the same time, only allow one cell at a time to be changed. If Target.Column <> 1 Or Target.Cells.Count > 1 Then Exit Sub 'Pressing the Delete key triggers the Change event. 'You might want to delete the cell's contents and start with 'an empty cell, so exit the Change event if the Delete key is pressed. If IsEmpty(Target) Then Exit Sub 'Even though a number is *supposed* to be entered into column A, 'never assume that will always happen because users do make mistakes. 'Provide for the attempt at a non-numeric entry and disallow it. If IsNumeric(Target.Value) = False Then 'Disable events because you are about to undo the non-numeric value, 'and Undo also triggers the Change event. Application.EnableEvents = False 'Execute the Undo so the non-numeric entry is deleted. Application.Undo 'Enable events again. Application.EnableEvents = True 'Remind the user with a Message Box that only numbers are allowed, 'and exit the Change event procedure with the Exit Sub statement. MsgBox "You entered a non-numeric value.", _ vbExclamation, _ "Please: numbers only in column A!" Exit Sub End If 'Now that all the reasonable safeguards have been met, 'Declare two Double type variables: 'one named OldVal for the numeric value that was in the cell 'before it got changed, and the other variable named NewVal 'for the numeric value that was just entered. Dim OldVal As Double, NewVal As Double 'Define the NewVal variable first, as it is the number that 'was just entered into the cell. NewVal = Target.Value 'Undo the entry in order to display the old (preceding) value. 'Again, this requires that you disable events in order to not 're-trigger the Change event while you are already in a Change event. Application.EnableEvents = False 'Execute Undo so the previous value is re-established. Application.Undo 'Define the OldVal variable which is possible to do now that 'the previous value has been restored. OldVal = Target.Value 'Programmatically enter into the cell the sum of the old previous value, 'plus the new last-entered value, by referring to those two variables 'in an arithmetic equation just as you would if they were numbers. Target.Value = OldVal + NewVal 'Enable events now that all the changes to the cell have been made. Application.EnableEvents = True End Sub
Tom’s Tutorials for Excel:Running Sum in Same Cell
Hello,
Thanks for this excellent VBA. Could you please show me how to make this VBA work on multiple columns? I need it to work on Columns I though M beginning on row 7.
Thanks,
Noel
Private Sub Worksheet_Change(ByVal Target As Range)
‘Only apply this effect to column A (column 1 in VBA-Speak).
‘At the same time, only allow one cell at a time to be changed.
If Target.Column 1 Or Target.Cells.Count > 1 Then Exit Sub
‘Pressing the Delete key triggers the Change event.
‘You might want to delete the cell’s contents and start with
‘an empty cell, so exit the Change event if the Delete key is pressed.
If IsEmpty(Target) Then Exit Sub
‘Even though a number is *supposed* to be entered into column A,
‘never assume that will always happen because users do make mistakes.
‘Provide for the attempt at a non-numeric entry and disallow it.
If IsNumeric(Target.Value) = False Then
‘Disable events because you are about to undo the non-numeric value,
‘and Undo also triggers the Change event.
Application.EnableEvents = False
‘Execute the Undo so the non-numeric entry is deleted.
Application.Undo
‘Enable events again.
Application.EnableEvents = True
‘Remind the user with a Message Box that only numbers are allowed,
‘and exit the Change event procedure with the Exit Sub statement.
MsgBox “You entered a non-numeric value.”, _
vbExclamation, _
“Please: numbers only in column A!”
Exit Sub
End If
‘Now that all the reasonable safeguards have been met,
‘Declare two Double type variables:
‘one named OldVal for the numeric value that was in the cell
‘before it got changed, and the other variable named NewVal
‘for the numeric value that was just entered.
Dim OldVal As Double, NewVal As Double
‘Define the NewVal variable first, as it is the number that
‘was just entered into the cell.
NewVal = Target.Value
‘Undo the entry in order to display the old (preceding) value.
‘Again, this requires that you disable events in order to not
‘re-trigger the Change event while you are already in a Change event.
Application.EnableEvents = False
‘Execute Undo so the previous value is re-established.
Application.Undo
‘Define the OldVal variable which is possible to do now that
‘the previous value has been restored.
OldVal = Target.Value
‘Programmatically enter into the cell the sum of the old previous value,
‘plus the new last-entered value, by referring to those two variables
‘in an arithmetic equation just as you would if they were numbers.
Target.Value = OldVal + NewVal
‘Enable events now that all the changes to the cell have been made.
Application.EnableEvents = True
End Sub
Sorry, after some Google searching I found the answer. Here is the code I’m using at the beginning of the VBA:
I replaced this line:
‘If Target.Column 9 Or Target.Cells.Count > 1 Then Exit Sub
With these lines:
Const START_COL As Integer = 9
Const END_COL As Integer = 13
If Target.Column = END_COL Then
End If
Thanks!
Oops copied if incomplete. Here it is.
Const START_COL As Integer = 9
Const END_COL As Integer = 13
If Target.Column = END_COL Then
End If
Apologies for this 3rd entry, but the copy/paste continues to miss the entire replace lines:
I replaced this line:
‘If Target.Column 9 Or Target.Cells.Count > 1 Then Exit Sub
With these lines:
Const START_COL As Integer = 9
Const END_COL As Integer = 13
If Target.Column START_COL And Target.Column END_COL Then Exit Sub
I am trying to enter multiple numbers at various times in , say cell A1, and wish to keep a running balance in cell A1 but on another sheet. As I enter the new number in A1 I don’t want the old number to be deleted in A1 on the balance sheet.
The net effect: I can start from zero each day and enter data as I receive it daily and a running balance will be shown in the same cell on another sheet.
I don’t think this works for me.
Thank You.
Can you give a few specifics? For example, what is the name of the sheet tab you are entering numbers in? And in what cell? What is the name of the sheet tab you want the calulation to take place? in what cell on that sheet? How do you want to “start from zero”? By hitting the delete key on one of the two cells (entry sheet or destination sheet?). A bit more info on what interface you are wanting to work with, and I’m sure a solution is possible.
Hi Tom,
I copied your code and it is working great! Thank you!
However, I would like to enter a number into cell L1 and then keep a running sum of L1 in K1. How would I do that?
Thank you in advance!
See if this does what you want.
Remember, delete a Change event in the worksheet module (if you have one in there already) before you install this one:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> “$L$1” Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then
Range(“K1”).ClearContents
Exit Sub
End If
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox “You entered a non-numeric value.”, _
vbExclamation, _
“Please: numbers only in cell L1!”
Exit Sub
Application.EnableEvents = True
End If
Dim OldVal As Double, NewVal As Double
NewVal = Target.Value: OldVal = Range(“K1”).Value
Range(“K1”).Value = OldVal + NewVal
End Sub
Thanks,Tom. Now I get a syntax error. 🙁
Does it matter if the data is in a linked table?
It worked without an error when I tested it. Might matter if the data is in a linked table. If you were to open a new workbook and plug the code into a fresh worksheet module, you’d see that it works fine. Something is going on with your workbook that is causing an error. When a syntax error occurs, the offending code line is highlighted in yellow when you click teh debug button. Because I cannot see your workbook, the line that is erroring might not have anything to do with the code itself but be about some other conflict in your workbook design. Beyond that I just cannot diagnose the error from a distance.
Thanks again. The syntax occurs in the first line. I opened the code in a brand new workbook and receive the same error.
Thank you for your assistance. Do you have any other ideas how this can be achieved?
It might be the double quotation marks that get changed in this WordPress platform from how I type them from my keyboard and how they get reproduced on this web page.
Take a close look at the code I posted, which you have copied and pasted into your worksheet module.
Examine each line for double quotes and where you find them, delete them and replace them by typing them in manually.
For example, the first line is
If Target.Address “$L$1” Or Target.Cells.Count > 1 Then Exit Sub
There are double quotes around $L$1 so edit that by changing the double quotes you see with manually entered double quotes.
Do the same for this line:
Range(“K1”).ClearContents
and this line:
MsgBox “You entered a non-numeric value.”, _
and this line:
“Please: numbers only in cell L1!”
and these 2 lines:
NewVal = Target.Value: OldVal = Range(“K1”).Value
Range(“K1”).Value = OldVal + NewVal
Should work.
It works! One more thing…how do you make it for the whole column? Not only cell L1.
Your request is not clear. Is it that you want to enter a number…
Possibility A
…anywhere in column L and have it always be added to what is in cell K1?
Possibility B
…anywhere in column L and have it always be added to what is in that row’s cell in column K?
Possibility C
…anywhere in column L and have it be added to what is in the next open cell in column K?
I believe B-anywhere in column L and added to the respective adjoining cell in column K in the same row as the input.
Remember to manually adjust the double quote characters.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 12 Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then
Cells(Target.Row, 11).ClearContents
Exit Sub
End If
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox “You entered a non-numeric value.”, _
vbExclamation, _
“Please: numbers only in column L.”
Exit Sub
Application.EnableEvents = True
End If
With Cells(Target.Row, 11)
Dim OldVal As Double, NewVal As Double
NewVal = Target.Value: OldVal = .Value
.Value = OldVal + NewVal
End With
End Sub
That’s AWESOME! Thank you sooo much for your help and patience. 🙂
This works great for what I’m trying to do….now how do I copy this to use in the next column?
I’m using this to add numbers in column “D” with the total in cell next to it in column “E”. Now I need to do the same for “F” to “G” and “H’ to “I” but I can’t figure out how to do it.
Is this for any cell (any means any) in columns D, F, and H to have their respective totals in columns E, G, and I, or is it only on one row in those columns (and if so, which row) where you want this special functionality.
Hi Tom, it is for any cell in those columns.
This in your worksheet module should do what you want.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Column
Case 4, 6, 8
If IsEmpty(Target) Then
With Target
Cells(.Row, .Column + 1).ClearContents
End With
Exit Sub
End If
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "You entered a non-numeric value.", 16, "Numbers only in this column."
Exit Sub
Application.EnableEvents = True
End If
Dim OldVal As Double, NewVal As Double
With Cells(Target.Row, Target.Column + 1)
NewVal = Target.Value: OldVal = .Value
.Value = OldVal + NewVal
End With
End Select
End Sub
Worked like a charm….you saved my a$$ Tom, thanks a million!!!
Happy to help out, Bonnie.
I’m using Vlookup to populate data into a table based on a validation list that rolls up into a graph. The user can select from 25 different data sets. User can choose up to four data sets at a time.Once a selection is made, it populates into a table. The graphs are generated from the table. The issue is that the majority of data is % and the tables’ cells are formatted for %. There are some that are numbers so when that data is drawn up, the number is formatted as % and not as a number. Is there a formula in conditional formatting that would automatically adjust the cell format when the data is formatted for number?
The short answer (I believe) is no. The reason is, native (non-VBA) formulas by themselves do not change the numerical formatting, nor do they identify a cell’s formatted Number property. Trying to follow your description of the project, it might be an issue of standardizing the style of the original numbers (whole, decimal, percentage, and so on) before they act on the worksheet cells. This may require some coding but I can’t tell for sure.
Is this something you can help me with? What info would you need?
I have a backlog of client projects happening; may I suggest you ask your question on a very popular Excel forum, to which I contribute, where many experts can help you quicker than I can at this time. Here’s the link, easy to register, no charge:
https://www.mrexcel.com/forum/index.php
If A1 contain value “2” and A1 = B2 and i want to put value “3” in A1 and B2 become 5 how?
Try this in your worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address <> "$A$1" Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then
Range("B2").ClearContents
Exit Sub
End If
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "You entered a non-numeric value.", _
vbExclamation, _
"Please: numbers only in cell A1!"
Exit Sub
End If
Dim OldVal As Double, NewVal As Double
NewVal = Target.Value
Application.EnableEvents = False
Application.Undo
OldVal = Target.Value
Target.Value = NewVal
Range("B2").Value = OldVal + NewVal
Application.EnableEvents = True
End Sub
Can you create the same effect on multiple cells in the same work sheet?
so A1 would affect B2
and A2 would affect B3, and so on
See previous comments to this same blog post about how I have already posted a solution for that.
Tom,
excellent work, the code is working like a charm.
i have wrecked my head for 4 days this program.
can you help me with the problem.
i am entering the data in A1 and B1=A1….i want to have the running total in the C1
eg. A1=3,B1=3,C1=3
A1=5,B1=5,C1=8
i have used the above code…while i enter the code in B cell the answer is coming
but when i enter it in A cell…the C cell is not updating.
That A1 cell is a sum of values in a cell from another working sheet.
Thanks
You wrote 2 opposite-sounding statements:
“…when i enter it in A cell…the C cell is not updating”
“That A1 cell is a sum of values in a cell from another working sheet”
So, 2 questions:
(1)
How does the number in cell A1 get into cell A1 — with a formula in cell A1 or do you manually enter it into cell A1.
(2)
Why does cell B1 have a formula that equals A1? What is the purpose of cell B1 other than to duplicate what is in cell A1?
Tom,
these are great tools, thank for all this.
I am having difficulty adjusting your useful coding to limit the running total to specific rows.
I need user to enter data into Column D, rows 17 thru 20. With corresponding running total to be updated in Column E, rows 17 thru 20.
I have used different range variations without success. I am not a programmer.
Danna – –
Replace the Change event code in your worksheet module (if you have not already deleted that code) with the following code, which should do what you want:
Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, Range("D17:D20")) Is Nothing Then Exit Sub
If IsEmpty(Target) Then
Cells(Target.Row, 5).ClearContents
Exit Sub
End If
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "You entered a non-numeric value" & vbCrLf & _
"in cell " & Target.Address(0, 0) & ".", 16, _
"Numbers only allowed in D17:D20."
Exit Sub
End If
Dim OldVal As Double, NewVal As Double
With Target
NewVal = .Value
Application.EnableEvents = False
Application.Undo
OldVal = Cells(.Row, 5).Value
.Value = NewVal
Cells(.Row, 5).Value = OldVal + NewVal
Application.EnableEvents = True
End With
End Sub
Works perfectly !
Tom,
This is great! I have used bonnies example except I need it to post a Join string in the column next to the total running total.
Basically I need a calculator Tape function with a , delimiter for each total – So that I can check accuracy of my inputs.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Column
Case 4, 7, 9
If IsEmpty(Target) Then
With Target
Cells(.Row, .Column + 1).ClearContents
End With
Exit Sub
End If
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox “You entered a non-numeric value.”, 16, “Numbers only in this column.”
Exit Sub
Application.EnableEvents = True
End If
Dim OldVal As Double, NewVal As Double
With Cells(Target.Row, Target.Column + 1)
NewVal = Target.Value: OldVal = .Value
.Value = OldVal + NewVal
End With
With Cells(Target.Row, Target.Column + 2)
.Value = NewVal
End With
End Select
End Sub
Good morning Tom,
Your first code (at the top of the thread) works great. Is there a way to apply it to Columns B through T?
Thanks
Hello Donny, here’s the code for columns B:T inclusive:
Private Sub Worksheet_Change(ByVal Target As Range)'Apply to columns B:T.
If Target.Column = 1 Or Target.Column > 20 Then Exit Sub
'Only one cell at a time can be changed.
If Target.Cells.Count > 1 Then Exit Sub
'OK to delete the cell contents.
If IsEmpty(Target) Then Exit Sub
'Mandate numeric entry.
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "You entered a non-numeric value.", 48, "Numbers only in B:T."
Exit Sub
End If
'Monitor number entries for running sum.
Dim OldVal As Double, NewVal As Double
NewVal = Target.Value
Application.EnableEvents = False
Application.Undo
OldVal = Target.Value
Target.Value = OldVal + NewVal
Application.EnableEvents = True
End Sub
Thats works great! Thanks so much Tom, I’ve been wracking my brain on this for 2 days now.
Cool – – thanks for the update.
Good morning Tom,
I have one more question if you dont mind.
If in the future I need to add more columns and have them function the same as B:T is there a part of this code that I can change to apply it to the added columns, or would a new code need to be written?
Thank you
The simplest answer, assuming you always want to start with column B, is to change the column number that is the right-most column that you want to monitor. You originally said it would be column T, which to Excel is column number 20 (T is the twentieth letter of the alphabet).
If you want to monitor from column B up to and including column X, you would change this line in my code
If Target.Column = 1 Or Target.Column > 20 Then Exit Sub
to this
If Target.Column = 1 Or Target.Column > 24 Then Exit Sub
If you want to monitor from column B up to and including column AG, you would change this line in my code
If Target.Column = 1 Or Target.Column > 20 Then Exit Sub
to this
If Target.Column = 1 Or Target.Column > 31 Then Exit Sub
Thats awesome. Once again, thanks Tom.
Donny
Good morning Tom,
I have another dilemma that I could use your assistance with.
I have two sheets, sheet 1 (Daily Quantities) And sheet 2 (Year To Date Quantities).
Numbers are added to sheet 1 through out the day and add in the same cells on sheet 2 (running total)
Every morning when I delete the numbers in sheet one to start a new day I lose the numbers in sheet 2.
Is there a way to make sheet 2 retain its numbers and continue adding each day
Thank you
Donny
How do the numbers in sheet 2 get there.
How (if in any way) does the range of numbers in sheet 2 correspond with the range of changing numbers in sheet 1.
The way I have it now is
Sheet 2-
=SUM(‘Sheet1’!B2)
You did not answer my second question.
I’m not 100% sure I understand your question but,
The numbers is sheet 2 change with the numbers in sheet 1 and when I delete the numbers in sheet1 they also disappear from sheet2
I get that you have formulas. I asked how the ranges correspond.
For example, you said in sheet2 is the formula =SUM(‘Sheet1’!B2). OK, so the formula in sheet2 looks at cell B2 of sheet1. I get that too.
My question is, where (what cell) is that formula in on sheet2? Is it correspondingly the same because it is in cell B2 of sheet2? Or is the formula in cell JX10054 of sheet2? How do the formula range locations in sheet2 correspond to the cell range locations in sheet2 that point to them? The logic of where formula cells are are in relationship to where source data cells are is the question.
My apologies I’m very new to excel
Yes Sheet1 B2 to sheet2 B2, B3 to B3 ect.. across many cell
What are the codenames for Sheet1 and Sheet2 (maybe they are Sheet1 and Sheet2 but checking to be sure). Not the tab names, but the code names.
What is the range of cells on sheet1 Daily Quantities that are being changed.
The code names are sheet1 and sheet4
The range is columns B though x, rows 3 through 60
Works like a charm, thank you. Very useful in a number of situations too. I’m trying to figure out how to execute the same concept, but rather than using numbers entered manually, am rapid-populating the cell with a calculated value driven off of a random number generator. If that seems like a 10-second easy answer, and you are inclined to reply, that would be great. Otherwise, thank you once again for all of the wisdom you share.
Thanks for your kind comment. My 9-second answer is actually a question, which is, how are these rapid-populating entries made, is it only in one cell, and at what interval between entries do you want to see a calculated result. I suspect that if rapid fire is rapid, an update now would less meaning meaning a split second later.
The rapid population is from one of the inputs to the calculated value being driven off of refreshing =RAND() with a simple macro that let’s me keep the pedal to the metal, or in this case, the ALT+Letter shortcut to the metal. Eyeballing it with a timer (so pretty unofficial), it looks to be spitting out at a rate of about 30 per second.
It is only to one single cell, yes.
I would like to be able to sum the values passing through the cell, perhaps to an adjacent cell as an accumulated total – ideally at the same rate, but if the way it were all tied together resulted in it being slowed down a bit, the speed of the calculations is very secondary to what I’m after.
Think I got it. First, added the following (continued below):
Option Explicit
Dim oldvalue As Double
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = “$A$5” Then
On Error GoTo fixit
Application.EnableEvents = False
If Target.Value = 0 Then oldvalue = 0
Target.Value = 1 * Target.Value + oldvalue
oldvalue = Target.Value
fixit:
Application.EnableEvents = True
End If
End Sub
Then I got around the formula-driven issue by creating a macro that included the other =rand() iteration macro within it, but then pasted it into my target cell (A5 in this case) as a numerical value.
It’s working.
Cool! Thanks for the follow-up, Mike!
Hi Tom,
I have a similar request to Mike’s. I’m trying to figure out how to execute the same concept, but rather than using numbers entered manually, am rapid-populating the cell with a calculated value driven off of a Real Time Data function (RTD). To conclude, the RTD cells (column G to I) is the source and the running total are displayed in P,Q,R columns.
Your help is much appreciated, as i’m not very versed as Mike.
Regards
Hello Nick – –
Just as a start, the worksheet=level event procedure that Mike uses is a Change event because as you saw, it executes when a number is entered manually. That is why the event code has as its first line
Private Sub Worksheet_Change(ByVal Target As Excel.Range)In the case you describe, the trigger would be when a calculation takes place, so you can use the worksheet-level Calculate event in the worksheet module, with this syntax:
Private Sub Worksheet_Calculate()'code goes here
End Sub
The thing to be aware of is that there was one cell of interest (A5) and you described 3 columns which probably mean several cells on the same row and maybe many cells in many other rows, depending on how many formulas you have. That is a different approach where maybe you need to loop through each resultant cell to handle their individual updates. But that is just a guess, as I don’t know how many cells are recalculated at the same time and hence how many resultant cells need to be updated.
Thanks for the comments! I tried the below code, but every time by RTD cell updates (G4 & I4), I get a Run-time error ‘424’ Object required. Ultimately, the running sum can be in G10, I10.
Private Sub Worksheet_Calculate()
If Intersect(Target, Range(“D17:D20”)) Is Nothing Then Exit Sub
If IsEmpty(Target) Then
Cells(Target.Row, 5).ClearContents
Exit Sub
End If
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox “You entered a non-numeric value” & vbCrLf & _
“in cell ” & Target.Address(0, 0) & “.”, 16, _
“Numbers only allowed in D17:D20.”
Exit Sub
End If
Dim OldVal As Double, NewVal As Double
With Target
NewVal = .Value
Application.EnableEvents = False
Application.Undo
OldVal = Cells(.Row, 5).Value
.Value = NewVal
Cells(.Row, 5).Value = OldVal + NewVal
Application.EnableEvents = True
End With
End Sub
Just to be clear, if we start out from the beginning on this issue, what exactly are you doing and what do you want the result to be. For example, is it correct, or incorrect, to say that you enter (as in, you type in a number) into cells G4 and I4 and you thereafter expect a result in cells G10 and I10. If not, then what is the scenario. Some of the code you pasted that does not work looks to me like it has nothing to do with what you are doing and what you want.
Yes you are correct, the input cells are G4 and I4. The inputs to these cells are from the RTD function, so I don’t enter inputs manually. The results are in G10 and I10.
Can I get this to work in a calculated cell? I don’t want to delete the cell as I am doing a sheet that compares the time it took an employee to do tasks, for instance they do “A” plan time (pulling from a table) is 10 minutes and they do it in 12 minutes. They are over by 2 on this task but they will do maybe up to 20 different tasks through out the day. Task C is planned take 5 minutes, they do it in 3 minutes. I am clearing all fields once an employee is done and then keying another, moving this data to another spreadsheet to track daily production, ect….
Trying to follow your scenario explanation, you should be able to use the Worksheet_Calculate event instead of the Worksheet_Change event that I showed. The code I posted for the Change event is maybe 90% applicable to the Calculate event but you would need to revise some of the code, and omit other parts such as the Target reference and replace it with the address of the actual cell of interest.
On the face of it, your question sounds do-able. Not knowing what you know (or don’t know) about event code programming like this, I would leave my reply at this point so as to not bore you with details you maybe already know about the syntax for revisions to the needed Calc event code. But certainly, if you have any questions in case you get stuck, please feel free to post back.
Tom,
This has been a huge help to me. I need a little more help with what I am trying to achieve.
I have this currently is doing exactly what I need. Entering data into C8 and getting the total in C9. What I need is B8 through G8 to total B9 through G9. Thanks in advance if you are able to help me.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address “$C$8” Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then
Range(“C9”).ClearContents
Exit Sub
End If
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox “You entered a non-numeric value.”, _
vbExclamation, _
“Please: numbers only in cell C8!”
Exit Sub
Application.EnableEvents = True
End If
Dim OldVal As Double, NewVal As Double
NewVal = Target.Value: OldVal = Range(“C9”).Value
Range(“C9”).Value = OldVal + NewVal
End Sub
Can you please clarify what cells should be the ones you enter data in, and what cells you want the running sum to be.
At first, the sum to enter data was C8 and the running sum is C9.
But this you wrote…
“What I need is B8 through G8 to total B9 through G9.”
…means the opposite, that the cells in row 8 are the running sum cells.
Which is which, and then I can know how to code this.
Thanks for the reply Tom. What I need is to be able to enter data in B8 through G8 and get individual running sum into B9 through G9. example, entering data into B8 would change the running sum in B9, data in C8 would change the running sum in C9, D8 would change D9…etc.
I hope this explained what I need a little better. Let me know if you need any more details.
Thanks,
Todd
See if this does what you want.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B8:G8")) Is Nothing Then Exit Sub
With Target
If .Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then
.Offset(1).ClearContents
Exit Sub
End If
If IsNumeric(.Value) = False Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "You entered a non-numeric value.", _
vbExclamation, _
"Please: numbers only in range B8:G8!"
Exit Sub
Application.EnableEvents = True
End If
Dim OldVal As Double, NewVal As Double
NewVal = .Value: OldVal = .Offset(1).Value
Target.Offset(1).Value = OldVal + NewVal
End With
End Sub
Thank you Tom! That is exactly what I needed. Worked perfectly.
If in the future I want expand the worksheet and have the same function happen under this function, say… B10 through G10 and have the sum populate on B11 through G10, how could I add that into the code?
You wrote:
“and have the sum populate on B11 through G10”
I assume you meant B11 through G11.
Change this
If Intersect(Target, Range(“B8:G8”)) Is Nothing Then Exit Sub
to this
If Intersect(Target, Range(“B8:G8, B10:G10”)) Is Nothing Then Exit Sub
and change this
“Please: numbers only in range B8:G8!”
to this
“Please: numbers only in cell ” & Target.Address(0, 0) & “!”