Forum Discussion
Save Previous Cell Value Of A Changed Cell In Excel
You need to rewrite this only:
xDCell.Value = xDic.Items(I)
Like this:
xDCell.Formula = xDic.Items(I).Value
Or for better management you may use this macro, it's on click solution:
Sub FormulasToValuesInSelection()
Dim rng As Range
For Each rng In Selection
If rng.HasFormula Then
rng.Formula = rng.Value
End If
Next rng
End Sub
- Select the Range, you want to convert into Value, then RUN the macro.
- You may use this VBA code with Command Button also.
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?
- 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).
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!
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.
Shelbie1288 I think that should be:
xDCell.Value = xDic.Items(I).Value
- have entered the code in view code but when the data is refreshing in C:C, F:F, L:L columns it's not saving in the prescribed column.
Data is refreshing from web site .- does it work at all? basically first I need to know if the issue is that the code doesn't work for you at all or if it a refresh issue. If the data is updated in the background and not triggering a worksheet_change event that could be a problem or maybe you added the code to a 'module' instead of the actual worksheet. It is hard to know without the workbook.
Thank you for the reply! when I do that, it forces the cell that should contain the previous value to be blank. The code is not working and if I try to type something into that value it makes it blank. any suggestions? Thanks again!
Shelbie1288 OK so I actually looked at the code and I think I know what is going on. I don't have Dictionary type in my Excel (maybe this was an add-in or defined in another sheet of you book) but I think I get it. Try returning that line back to the original:
xDCell.Value = xDic.Items(I)
and down much lower change this line:
xDic.Add xRgArea(J).Address, xRgArea(J).Formula
to
xDic.Add xRgArea(J).Address, xRgArea(J).Value
crossing my fingers ...
