Forum Discussion

Shelbie1288's avatar
Shelbie1288
Copper Contributor
Oct 26, 2020

Save Previous Cell Value Of A Changed Cell In Excel

  I am trying to save the values of one column (G) to another (D) once I update it to a new value.  I have the code correct, however the cell I want to save contains a formula and it is uploading th...
excel
Formulas and Functions
Macros and VBA
Shelbie1288's avatar
Shelbie1288
Copper Contributor
Oct 26, 2020

Rajesh_Sinha 

thank you very much for the help! I have tried the fix you suggested of changing the incorrect code to :

xDCell.Formula = xDic.Items(I).Value

however the code will now not produce the previous cell anymore.  no errors were given, but the code does not produce anything.  any suggestions?

Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Oct 27, 2020
This address your issue,,, "save the value of the formula, not the formula itself.",,, I've suggested one more Macro "Sub FormulasToValuesInSelection()",, you may use it after you finish with Worksheet Change event Macro,, on the range you are getting New value (Formula).
  • fadomas313's avatar
    fadomas313
    Copper Contributor
    Dec 24, 2020

    Shelbie1288 mtarler  Rajesh_Sinha Would be so kind to update the code with this feature (value of formula not formual)? Also do you know how is it possible to trigger the event with any kind of cell change ?

     

    In my case what I want to accomplish is maintain a log of all the changes, I update cells in column E with a formula and then I want to maintain the old values in columns F, G, H and so on...

     

    And something last, my first 5 rows are headers, is there a way to apply any code starting from row 6?

     

    Many thanks to you all guys for sharing your stardust with us!

     

    • JMB17's avatar
      JMB17
      Bronze Contributor
      Dec 25, 2020

      fadomas313 

       

      If track changes is not a good option and if using cell comments to store the current cell value is acceptable, you could take a look at the attached workbook. The code is in the sheet1 module and the thisworkbook module.

       

      • fadomas313's avatar
        fadomas313
        Copper Contributor
        Dec 29, 2020

        JMB17 Many thanks!!!! This is exactly what I need!  Only thing is that the input is not manual but it´s related to another cell which is located to a different worksheet. To be precise I use this worksheet to concentrate data from another 3-4 worksheets. Do you know how can I trigger the event like this? 

         

        Also Do you know how can I start inserting the "history" data at the column G and after?

         

        Many thanks again, incredible!!! 😄

    • fadomas313's avatar
      fadomas313
      Copper Contributor
      Dec 24, 2020

      And something last, my first 5 rows are headers, is there a way to apply any code starting from row 6?

       

      • mtarler's avatar
        mtarler
        Silver Contributor
        Dec 24, 2020

        fadomas313 again, untested, but I included this ability into the code.  If you look at my comment on the lines near the top:

        Const xSource = "G:G, L:L"  'This is a list of columns that need to be saved
Const xTarget = "D:D, H:H"  'This is a matching list of columns where to save

        Those should actually be ranges so you should be able to do something like this:

        Const xSource = "E6:Z1000"  'This is a list of columns that need to be saved
Const xTarget = "F6:AA1000"  'This is a matching list of columns where to save

         as for any kind of cell change I don't understand what you mean.

        That all said, there is a track changes feature in excel.  This is a legacy command and can show you changes in another sheet or as comments.  Here is a webinar I bought (actually my employed bought) and watched but I'm sure there are others:

        https://www.complianceiq.com/trainings/livewebinar/3707/excel-spreadsheets-ensuring-data-integrity-and-21-cfr-part-11-compliance 

         

         

Resources