Previous value of cell before change

[sai]

When a cell value is changed and the worksheet change event is
triggered - is there a way I can get the previous value in the cell
before it was changed?

sai

 

[Frank Kabel]

Hi
you could use a static variable to store the previous result. Try something
like
Private Sub Worksheet_Change(ByVal Target As Range)
Static old_value
If Target.Address = "$A$1" Then
MsgBox "Old value: " & old_value
old_value = Target.Value
End If
End Sub

 

[sai]

Frank,

The problem is that it is just not one cell. I have a whole column (and
maybe multiple columns) of numbers for which I will need to know the
previous value. I do not want to carry the same data that is in the
spreadsheet in memory in an array.

The reason I need this is to be able to do a delta. I want to know the
incremental change made - based on which I want to update another cell.

When I use application.undo, the previous value is restored. Is there a way
I can access the undo object (?) to get the previous value?

sai

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldVal as Variant, NewVal as Variant
if target.count > 1 then exit sub
Application.EnableEvents = False
newval = Target.Value
application.Undo
oldval = Target.Value
if isnumeric(OldVal) and isnumeric(NewVal) then
target.offset(0,1).Value = NewVal - OldVal
end if
Target.Value = NewVal
Application.EnableEvents = True
End Sub

You may want to limit the range in which this occurs.