Is there a way to keep the current total when resetting/deleting cells?
Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 42

Thread: Is there a way to keep the current total when resetting/deleting cells?

  1. #21
    Board Regular
    Join Date
    Oct 2015
    Location
    South Wales
    Posts
    549
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to keep the current total when resetting/deleting cells?

    You need some "event" to trigger it. In the example we've already done, the macro does the deleting too, so can check the number of job sites prior to deletion, and store the totals. But if rows are to be deleted manually, the totals won't be updated.

    Same applies to this new example - how will the macro know that source data is about to be deleted?

  2. #22
    Board Regular
    Join Date
    Nov 2017
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to keep the current total when resetting/deleting cells?

    Oooo, i think i get what you mean. So, there isn't a basic macro or formula that i can do that will just save the current totals? like if the sheet i am wanting to do says a total of 10, but the source sheet auto deletes and goes down to 3, there isn't a way to make it keep saying 10, like prevent it from subtracting, only adding. Or would i have to use a similar macro on a different sheet?

  3. #23
    Board Regular
    Join Date
    Oct 2015
    Location
    South Wales
    Posts
    549
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to keep the current total when resetting/deleting cells?

    A formula will just keep calculating. The original macro stored the number of deleted records in a seperate set of cells, adding to that number each time a record was auto-deleted by the macro. We then amended the formulae to add in the running totals from the deleted records cells.

    What the original macro won't solve is where you may need to delete a record manually. This is because if the record isn't being deleted by the macro, the macro isn't updating the set of cells containing the number of deleted records.

    There is a way (with more macros) that you can get around this! This involves two more macros:
    • A macro that automatically runs whenever the selected cell on the worksheet changes. This stores the values of the three cells in column M in a different column (I'll use O).
    • A macro that automatically runs whenever a cell value changes on the worksheet (i.e. it doesn't run if you're just moving around the worksheet). This looks at the "new" values of the three cells in column M and compares them to the "old" values which were stored in column O by the previous macro. If the values have gone down (i.e. a record has been blanked/deleted), it will increase the count of deleted records in column O, which adds back into the formulae in column M.

    You would also need to amend the original macro, so that when it auto-deletes, it doesn't also trigger the "cell value change" macro above, as this may potentially duplicate the adjustments.

    There is a potential issue with this - you'll need to consider whether it makes it too risky! The new macros run on any amendment to the sheet, not just a deletion! So if a new record is input as Job Site 1, then the user realises this was wrong and overtypes it as Job Site 2, the macro will assume that a Job Site 1 record is being deleted, and adjust the totals accordingly. Also bear in mind that there will be macros running in the background all the time - if your spreadsheet is huge, this may make it run a little bit slower.

    But if you do want to go ahead...:

    First step is to adjust the original macro. On the row immediately above Do put:
    Code:
    Application.EnableEvents = False
    On the row immediately below Loop put:
    Code:
    Application.EnableEvents = True
    These two rows stop other automated macros from running while the original macro is doing its deletions and adjustments

    Next step is the new macros. These need to be put in the part of the VBA editor for the sheet containing the records, which I think from your previous posts is
    Sheet38(Job Data) -
    they should not be put with the original macro in ThisWorkbook:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        'Active cell on worksheet has changed.  Stop other automatic macros running whilst this one is making adjustments.
        Application.EnableEvents = False
        
        'Store the current values of column M cells in column O
        Range("O10").Value = Range("M10").Value
        Range("O11").Value = Range("M11").Value
        Range("O12").Value = Range("M12").Value
        
        'Re-enable other automatic macros
        Application.EnableEvents = True
        
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        'A change has taken place on the worksheet.  Stop other automatic macros running whilst this one is making adjustments.
        Application.EnableEvents = False
        'Calculate any change in the values in column M by comparing them with the old values stored in column O by the other macro.
        DifferenceM10 = Range("O10").Value - Range("M10").Value
        DifferenceM11 = Range("O11").Value - Range("M11").Value
        DifferenceM12 = Range("O12").Value - Range("M12").Value
        
        'If the cell value has reduced, the total for deleted records needs to be increased by the difference
        If DifferenceM10 > 0 Then Range("N10").Value = Range("N10").Value + DifferenceM10
        If DifferenceM11 > 0 Then Range("N11").Value = Range("N11").Value + DifferenceM11
        If DifferenceM12 > 0 Then Range("N12").Value = Range("N12").Value + DifferenceM12
        
        'Store the current values of column M cells in column O
        Range("O10").Value = Range("M10").Value
        Range("O11").Value = Range("M11").Value
        Range("O12").Value = Range("M12").Value
        
        'Re-enable other automatic macros
        Application.EnableEvents = True
    End Sub
    I've done these macros based on the Job Site sheet, as I know what the cell ranges used are. But you should be able to adapt this for other sheets if you want.

    Final thought - one of your previous posts mentioned deleting columns! Remember that all the cell ranges are written into the macros, so if you start deleting columns on the sheet, figures may appear in places that you're not expecting them to!


  4. #24
    Board Regular
    Join Date
    Nov 2017
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to keep the current total when resetting/deleting cells?

    Thanks, i'll look and tinker with it. Largely the columns don't get changed on the main A - k. I recently made some changes but they have been the same for years and needed to be updated. SO when we do have to add things like drop down data etc we do it off to the side. So, i should be ok. I'll give it a shot. I know i had another thread with trying to get some help with sumproducts, i need to check in there, was having an issue with getting 1 formula to work perfectly But its mostly there.

  5. #25
    Board Regular
    Join Date
    Nov 2017
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to keep the current total when resetting/deleting cells?

    Haven't gotten to play with it yet, been a little busy but i am gonna try over the next few days.

  6. #26
    Board Regular
    Join Date
    Nov 2017
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to keep the current total when resetting/deleting cells?

    Ok, finally got around to playing with it, but have an issue i think. But not sure if i missed something your you typed N10 instead on M10?

    So i have made some adjustments to the sheet, A - K is still all the same, and i was able to modify your original macro to compensate so that is all good. So the current column i have the MX being tracked is in column W, 4 thru 15. I created a similar setup as we did for the other macro where as it deletes it goes into another column, in this case is AA 4 thru 15 and adjusts as it deletes. But i can quite get this one to work the same way. Am i just missing something?

  7. #27
    Board Regular
    Join Date
    Oct 2015
    Location
    South Wales
    Posts
    549
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to keep the current total when resetting/deleting cells?

    Columns M and N in the new macro do the same as the did in the original one - N is the number of deleted rows, and M is the number of rows still existing for that job site plus the figure in N. In the original macro, I could count the number of rows that would be deleted before they actually were, and increase the value in N accordingly. The new macro can't do that, as it reacts to a change that has just happened. That's why I needed column O - that is regularly updated with the value of the formula in M (rather than the formula itself). When something on the spreadsheet changes that affects the result in column M, the value in column O will have remained the same. The difference between M and O will be the number of rows deleted, and N can be increased by that.

    You've mentioned that you can't get what you've done to work the same way. What's happening, and are you using the new or old macro?

  8. #28
    Board Regular
    Join Date
    Nov 2017
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to keep the current total when resetting/deleting cells?

    Hmm, think i need to make adjustments. As the original macro deletes the rows by date, i can see the maintenance tallys decreasing, but not auto adding to the other columns to track its overall totals.

  9. #29
    Board Regular
    Join Date
    Nov 2017
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to keep the current total when resetting/deleting cells?

    I am playing with it, but still kicking my butt a little. Sorry for all the questions, trying to learn some VBA as i go along with you. What would column N represent? I am guessing that i may need to incorporate that as well?

    I thought i had it working at first, but then i noticed that it was tracking every time i moved the cursor to a new cell, so i was off on something haha.

    Not sure if i just have something out of place at the moment?

    I can try and copy in how i have altered it, might be easy to see the mistake that way?

  10. #30
    Board Regular
    Join Date
    Nov 2017
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to keep the current total when resetting/deleting cells?

    this is how i currently have it typed in,

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Active cell on worksheet has changed. Stop other automatic macros running whilst this one is making adjustments.
    Application.EnableEvents = False

    'Store the current values of column W cells in column AA
    Range("AA4").Value = Range("W4").Value
    Range("AA5").Value = Range("W5").Value
    Range("AA6").Value = Range("W6").Value
    Range("AA7").Value = Range("W7").Value
    Range("AA8").Value = Range("W8").Value
    Range("AA9").Value = Range("W9").Value
    Range("AA10").Value = Range("W10").Value
    Range("AA11").Value = Range("W11").Value
    Range("AA12").Value = Range("W12").Value
    Range("AA13").Value = Range("W13").Value
    Range("AA14").Value = Range("W14").Value
    Range("AA15").Value = Range("W15").Value

    'Re-enable other automatic macros
    Application.EnableEvents = True

    End Sub


    Private Sub Worksheet_Change(ByVal Target As Range)

    'A change has taken place on the worksheet. Stop other automatic macros running whilst this one is making adjustments.
    Application.EnableEvents = False
    'Calculate any change in the values in column W by comparing them with the old values stored in column AA by the other macro.
    DifferenceM10 = Range("AA4").Value - Range("W4").Value
    DifferenceM11 = Range("AA5").Value - Range("W5").Value
    DifferenceM12 = Range("AA6").Value - Range("W6").Value
    DifferenceM12 = Range("AA7").Value - Range("W7").Value
    DifferenceM12 = Range("AA8").Value - Range("W8").Value
    DifferenceM12 = Range("AA9").Value - Range("W9").Value
    DifferenceM12 = Range("AA10").Value - Range("W10").Value
    DifferenceM12 = Range("AA11").Value - Range("W11").Value
    DifferenceM12 = Range("AA12").Value - Range("W12").Value
    DifferenceM12 = Range("AA13").Value - Range("W13").Value
    DifferenceM12 = Range("AA14").Value - Range("W14").Value
    DifferenceM12 = Range("AA15").Value - Range("W15").Value

    'If the cell value has reduced, the total for deleted records needs to be increased by the difference
    If DifferenceM10 > 0 Then Range("AG4").Value = Range("AG4").Value + DifferenceM10
    If DifferenceM11 > 0 Then Range("AG5").Value = Range("AG5").Value + DifferenceM11
    If DifferenceM12 > 0 Then Range("AG6").Value = Range("AG6").Value + DifferenceM12

    'Store the current values of column W cells in column AA
    Range("AA4").Value = Range("W4").Value
    Range("AA5").Value = Range("W5").Value
    Range("AA6").Value = Range("W6").Value
    Range("AA7").Value = Range("W7").Value
    Range("AA8").Value = Range("W8").Value
    Range("AA9").Value = Range("W9").Value
    Range("AA10").Value = Range("W10").Value
    Range("AA11").Value = Range("W11").Value
    Range("AA12").Value = Range("W12").Value
    Range("AA13").Value = Range("W13").Value
    Range("AA14").Value = Range("W14").Value
    Range("AA15").Value = Range("W15").Value

    'Re-enable other automatic macros
    Application.EnableEvents = True
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •