Tom’s Tutorials for Excel: Running Sum in Same Cell

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
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
63 comments on “Tom’s Tutorials for Excel: Running Sum in Same Cell
  1. Noel says:

    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

  2. Noel says:

    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!

    • Noel says:

      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

      • Noel says:

        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

  3. Duane says:

    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.

    • Tom Urtis says:

      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.

  4. Kat says:

    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!

    • Tom Urtis says:

      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

      • Kat says:

        Thanks,Tom. Now I get a syntax error. 🙁
        Does it matter if the data is in a linked table?

        • Tom Urtis says:

          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.

          • Kat says:

            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?

            • Tom Urtis says:

              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.

  5. Kat says:

    It works! One more thing…how do you make it for the whole column? Not only cell L1.

    • Tom Urtis says:

      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?

      • Kat says:

        I believe B-anywhere in column L and added to the respective adjoining cell in column K in the same row as the input.

        • Tom Urtis says:

          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

          • Kat says:

            That’s AWESOME! Thank you sooo much for your help and patience. 🙂

          • Bonnie says:

            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.

            • Tom Urtis says:

              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.

          • Bonnie says:

            Hi Tom, it is for any cell in those columns.

            • Tom Urtis says:

              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

          • Bonnie says:

            Worked like a charm….you saved my a$$ Tom, thanks a million!!!

  6. Kat says:

    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?

    • Tom Urtis says:

      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.

  7. Abdulqayyum says:

    If A1 contain value “2” and A1 = B2 and i want to put value “3” in A1 and B2 become 5 how?

    • Tom Urtis says:

      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

      • jazzy says:

        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

      • Pavan Ramaswamy says:

        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

        • Tom Urtis says:

          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?

  8. Danna says:

    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.

    • Tom Urtis says:

      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

  9. Danna says:

    Works perfectly !

  10. Tyson says:

    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

  11. Donny says:

    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

    • Tom Urtis says:

      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

  12. Donny says:

    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

    • Tom Urtis says:

      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

  13. Donny says:

    Thats awesome. Once again, thanks Tom.

    Donny

  14. Donny says:

    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

  15. Donny says:

    The way I have it now is
    Sheet 2-
    =SUM(‘Sheet1’!B2)

  16. Donny says:

    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

    • Tom Urtis says:

      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.

  17. Donny says:

    My apologies I’m very new to excel
    Yes Sheet1 B2 to sheet2 B2, B3 to B3 ect.. across many cell

    • Tom Urtis says:

      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.

  18. Donny says:

    The code names are sheet1 and sheet4
    The range is columns B though x, rows 3 through 60

  19. Mike says:

    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.

    • Tom Urtis says:

      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.

  20. Mike says:

    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.

    • Mike says:

      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.

  21. Nick says:

    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

    • Tom Urtis says:

      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.

      • Nick says:

        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

        • Tom Urtis says:

          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.

          • Nick says:

            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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*