Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Previous value of cell before change
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Previous value of cell before change
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 -- Regards Frank Kabel Frankfurt, Germany "sai" schrieb im Newsbeitrag ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Previous value of cell before change
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 "Frank Kabel" wrote in message ... 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 -- Regards Frank Kabel Frankfurt, Germany "sai" schrieb im Newsbeitrag ups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Previous value of cell before change
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. -- Regards, Tom Ogilvy "sai" wrote in message ... 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 "Frank Kabel" wrote in message ... 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 -- Regards Frank Kabel Frankfurt, Germany "sai" schrieb im Newsbeitrag ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3-Color Scale Vlookup for Current Month/Previous/Pre-Previous | Excel Discussion (Misc queries) | |||
How can I recover the previous data after save change | Excel Worksheet Functions | |||
Can I change column width for remainder but NOT previous text? | Excel Discussion (Misc queries) | |||
Copied dates automatically change to previous day | About this forum | |||
change cell shading whenever contents different from previous cell | Excel Discussion (Misc queries) |