Record a Cell's Prior Value: Can It Be Done?

[Frith]

Greetings,


Could it be possible to "sequentially offset" a vlaue, say, of A1 such that, for instance, when A1 changes from "5" to "6" (via formula, not direct input), A1=6 and, say, B1=5, and so on? You know, like keeping a value record of A1, one step back, in B1.

This would be UNBELIEVABLY valuble to me. Thus, would I be in eternal debt to he/she who would resolve this here.


Thank you,
I am Frith of Whales and I have a case of the Mondays.

 

[MickG]

Hi, Right Click Sheet Tab ,Select View Code, VB Window appears.
Paste all this code at the TOP of the VB Window.
Close window
When you Update "A1" by Formula The previous "A1" value will show in "B1" etc.

[COLOR="Navy"]Dim[/COLOR] temp
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Last [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Application.EnableEvents = False
Last = Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Column
[COLOR="Navy"]If[/COLOR] Range("A1") <> temp [COLOR="Navy"]Then[/COLOR] Cells(1, Last) = temp
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
temp = [a1]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Frith]

Hey, thanx a lot, that rocks! Olnly one thing; is it possible to, instead of shifting the "prior value" to the right each time in a new cell, could these all stay in one cell, say B1 (I don't need to keep them after each update)? It'd just save me a bunch of =IF/=VLOOKUP formulas.

Thanx again,
Frithlebane

 

[lenze]

What about a comment??
Tracking Changes with Comments

lenze

 

[Frith]

Hi,

I thought about that, but I need to USE the prior cell data in another formula (unless there's a way to concatenate a particular piece of a comment).

Frithlebane Gwynlethrend

 

[Frith]

Hi again...

Oh, MickG, disregard that shifting problem, I got it now... However, do you know how I might ADD a second cell that records A2's prior (formulaic-induced) values? I'd like it to go into and remain constant at I2. I just wasn't sure were to type the I2 range reference w/o monkey-wrenching the macro. I tried concatenating the original code from Sheet 2 w/updated cell references, but it wouldn't take.

Thanx a great deal,
Frith


This is working great:

Dim temp
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Last As Integer
Application.EnableEvents = False
Last = Cells(5, Columns.Count).End(xlToLeft).Offset(, 8).Column
If Range("A1") <> temp Then Cells(1, Last) = temp
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
temp = [a1]
End Sub

 

[MickG]

Hi, Here's the Modified Code, there's a Couple of option Lines you can try , Hopefully you'll Get what you want.

[COLOR="Navy"]Dim[/COLOR] Temp, Temp2
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Last [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Range("A1") <> temp [COLOR="Navy"]Then[/COLOR] Range("B1") = temp
'[COLOR="Green"][B]If Range("A2") <> Temp2 Then Range("I2") = Temp2 ' Just previous "A2" value[/B][/COLOR]
If Range("A2") <> Temp2 Then Range("I2") = Range("c2") & Temp2 & "," '[COLOR="Green"][B] All Previous Values "A2"[/B][/COLOR]
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
temp = [a1]
Temp2 = [a2]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Frith]

Thanx again, MickG - 1st option works great!

 

[Frith]

Hi MickG,


If you're still out there, there's a problem w/that code. It's my oversight, but it doesn't take "=" values only non-matching ("<>") ones. I tried putting in this code...

If Range("I3") <> Temp Or Range("I3") = Temp Then Range("I1") = Temp

...but it really screws things up when I double click on cells, for some reason. Do you know how to integrate an "=" operator w/the "<>" ones???


Thanx yet again,
Frith

 

[shaneherron]

Hi Firth,
Instead of using the <> sign, try using a not statement. It's like saying if X doesn't equal Y. Example:

If Not range("i3") = Temp then Range("I1") = Temp