Forum Discussion
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 that to column D. I want to save the value of the formula, not the formula itself. Is there a way to alter the VBA code below to do so? (or a whole new code in general! I'm not picky!) Thank you all SO MUCH in advance!!!!
Dim xRg As Range
Dim xChangeRg As Range
Dim xDependRg As Range
Dim xDic As New Dictionary
Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long
Dim xCell As Range
Dim xDCell As Range
Dim xHeader As String
Dim xCommText As String
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
xHeader = "Previous value :"
x = xDic.Keys
For I = 0 To UBound(xDic.Keys)
Set xCell = Range(xDic.Keys(I))
Set xDCell = Cells(xCell.Row, 4)
xDCell.Value = ""
xDCell.Value = xDic.Items(I)
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim I, J As Long
Dim xRgArea As Range
On Error GoTo Label1
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set xDependRg = Target.Dependents
If xDependRg Is Nothing Then GoTo Label1
If Not xDependRg Is Nothing Then
Set xDependRg = Intersect(xDependRg, Range("G:G"))
End If
Label1:
Set xRg = Intersect(Target, Range("G:G"))
If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) Then
Set xChangeRg = Union(xRg, xDependRg)
ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) Then
Set xChangeRg = xDependRg
ElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) Then
Set xChangeRg = xRg
Else
Application.EnableEvents = True
Exit Sub
End If
xDic.RemoveAll
For I = 1 To xChangeRg.Areas.Count
Set xRgArea = xChangeRg.Areas(I)
For J = 1 To xRgArea.Count
xDic.Add xRgArea(J).Address, xRgArea(J).Formula
Next
Next
Set xChangeRg = Nothing
Set xRg = Nothing
Set xDependRg = Nothing
Application.EnableEvents = True
End Sub
- If track changes is not a viable option, have you considered storing the previous value in a cell comment instead? A cell comment would always be tied to the cell and would not need to be constantly refreshed with each selection_change as the dictionary does in order to keep the last value synced to it's particular cell. If you still wanted the last value to appear in another cell, you could transfer the value stored in the cell comment to the other cell - it appears you're only keeping one level of previous values?
You need to rewrite this only:
xDCell.Value = xDic.Items(I)Like this:
xDCell.Formula = xDic.Items(I).ValueOr 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).
Here are two possible solutions that you could use in brief. The first is for the current cell. Even if several cells are selected, only the current cell is converted:
Sub RngFormelZuWert ()
Dim c As Range
For Each c In Selection
c.Value = c.Value
Next c
End SubYes, you can also do without the word Value, because in this case it is the default property. But better safe than sorry, and this is how it will work 10 years from now.
The second possibility would be that all cells in a marked area should be "treated" in this way. Then this code leads to the goal:
Sub RngFormelZuWert ()
Dim c As Range
For Each c In Selection
c.Value = c.Value
Next c
End SubYou can see that this is hardly more code than in the first example.
Is for everyone who would like to have it short and sweet:Sub Sel2Val ()
Selection.Value = Selection.Value
End SubI hope that I was able to help you further, or to provide a solution.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Thank you for the reply! I do not know how to write vba code at all so all I do is copy and paste what is given to me. I have done so with the code you provided and nothing happened. I am trying to have column D record the previous value (NOT formula) of Column G. could you tell me what to change to your code written to do so? again thank you so so much for the help! I greatly appreciate it
