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!
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 .
