Remember the previous value of formulas

Question

After a deep research on the internet I managed to find a VBA code that allows me to remember the previous result of a formula. I would like to modify this code to obtain the previous value of the formulas in one column in another column next to it. For example: if '' B2: B80 "contains formulas, I would like" D2: D80 "to show the previous value of those formulas.

The code that I show does not keep the previous values in a single cell but continuously populates a column down and my goal is to obtain the previous value of each formula in a single cell, but of several cells of a column.

Dim xVal As String
Private Sub Worksheet_Change(ByVal Target As Range)
Static xCount As Integer
Application.EnableEvents = False
If Target.Address = Range("C2").Address Then
Range("D2").Offset(xCount, 0).Value = xVal
xCount = xCount + 1
Else
If xVal <> Range("C2").Value Then
Range("D2").Offset(xCount, 0).Value = xVal
xCount = xCount + 1
End If
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
xVal = Range("C2").Value
End Sub

Variatus · Answer 1 · 2020-05-18T02:08:19.963

0

Please try this simple code. I think it will do what you want.

Sub CopyValues()

    With Worksheets("Sheet1")               ' enter your tab's name here
        .Range("B2:B80").Copy
        .Cells(2, "D").PasteSpecial xlValues
    End With
    Application.CutCopyMode = False
End Sub

edited May 18 '20 at 02:08

answered May 18 '20 at 00:33

Variatus

14,293
2
14
30

How much I would like to tell you dear friend that the code worked for me but it is not, in fact, there is no answer in the worksheet. – iPerry May 18 '20 at 01:32
1

I tested it. It works. Note that it works on the active sheet. You may have given it a blank sheet to work on. This kind of error is very common and often very destructive. To avoid it, the sheet should be named. I will add code to specify the sheet. – Variatus May 18 '20 at 02:05
I made the agreed changes, all as you mention. Without results. I tried putting the code in a module and it only shows the values in "D" when I touch play on the console. Nothing else later. :( – iPerry May 18 '20 at 02:51
What do you mean by "touch play on the console". Of course you need to install the code (in a standard code module) so that you can run it, and run it for the code to work. I now see that you were used to an event procedure. We might fix that but what's the "event". If you run it too frequently it may defeat its purpose. Perhaps record yesterday's values when you first open the workbook in the morning? Think also about when you do NOT want it to run. – Variatus May 18 '20 at 03:32

score 0 · Answer 2 · answered May 18 '20 at 06:38

i use something similar to track changes on another sheet. Maybe this will help?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
sSheetName = "Data"
If ActiveSheet.Name <> "LogDetails" Then
Application.EnableEvents = False
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " - " & Target.Address(0, 0)
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
Sheets("LogDetails").Columns("A:E").AutoFit
Application.EnableEvents = True
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo ErrHandler:
    n = 1 / 0
    Debug.Print n
    oldValue = Target.Value
    oldAddress = Target.Address
    Exit Sub
ErrHandler:
    n = 1
    ' go back to the line following the error
      Resume Next
    oldValue = Target.Value
    oldAddress = Target.Address
End Sub

This tracks each change made in all sheets bar the LogDetails so would record all your changes.

I believe if you add the last sub into yours and change the reference it should work.

Remember the previous value of formulas

2 Answers2