Record previous cell value

[Retroshift]

Hello,

I am looking for VBA code which copies the old cell text value of cell A1 (the value of A1 is returned by a formula which references to other cells) to cell A2, every time cell A1 contains a new and updated value.
(The value of cell A2 should also remain unchanged whenever cell A1 is selected.) I have been looking online but cannot find the right script. Thanks

 

[My Aswer Is This]

Hello,

I am looking for VBA code which copies the old cell text value of cell A1 (the value of A1 is returned by a formula which references to other cells) to cell A2, every time cell A1 contains a new and updated value.
(The value of cell A2 should also remain unchanged whenever cell A1 is selected.) I have been looking online but cannot find the right script. Thanks

Are you saying whenever a change is made to Range("A1") you want that value entered into Range("A2")
And the change is made as a result of a manual change and not the result of a formula causing the change

And just for clarity selecting a cell and changing the value in a cell are not the same.

 

[Retroshift]

Are you saying whenever a change is made to Range("A1") you want that value entered into Range("A2") => the old value of A1 before the change to the value of A1 should be entered in A2

And the change is made as a result of a manual change and not the result of a formula causing the change => no, the change of cell A1 is made as a result of a third cell being changed which alters the value of cell A2 because of a formula in A2 which references to this third cell; it is no manual change

And just for clarity selecting a cell and changing the value in a cell are not the same. => I know but I saw basic VBA code that changed cell A2 when cell A1 was selected. This is not what the code should do. The recorded old value should stay in cell A2 and can only be changed (to a more recent old value) if the value of cell A1 changes once again.

 

[Retroshift]

Anyone?

 

[My Aswer Is This]

I Tried to help and asked a few questions but did not understand your answers.

Like I asked:
Are you saying whenever a change is made to Range("A1") you want that value entered into Range("A2")
And the change is made as a result of a manual change and not the result of a formula causing the change

But you did not say "Yes" or "No"


Here is another question.
Are these changes made as the result of an entry you make or the result of a formula changing the value.
Would you just say "entry change" or "Calculation change"

 

[Retroshift]

I Tried to help and asked a few questions but did not understand your answers.

Like I asked:
Are you saying whenever a change is made to Range("A1") you want that value entered into Range("A2")
And the change is made as a result of a manual change and not the result of a formula causing the change

But you did not say "Yes" or "No"


Here is another question.
Are these changes made as the result of an entry you make or the result of a formula changing the value.
Would you just say "entry change" or "Calculation change"

Are you saying whenever a change is made to Range("A1") you want that value entered into Range("A2") => yes (after every change in A1, range A2 has to be overwritten by the previous value of A1)
And the change is made as a result of a manual change and not the result of a formula causing the change => no, the change to A1 is made as a result of a formula
Are these changes made as the result of an entry you make or the result of a formula changing the value. => I would say a calculation change since the value of A1 changes based on a formula referencing to a third cell; there is no manual input in cell A1

 

[My Aswer Is This]

I think I now understand

You want a script to Run when the value in Range("A1") changes
And this change occurs as the result of a formula in Range("A1")
But you want the value that was in "A1" prior to the change in Range("A1") copied To Range("A2")
And you want nothing to happen if Range("A1") is selected.

I really do not know how a script could do that. The script would need to store the current value of Range("A1") some place prior to the change in A1 so it can put this value in Range("A2") when the change occurs in A1

 

[Retroshift]

That is exactly what I mean. Two things could help: I already have a macro button which refreshes the sheet; and as an alternative to not being allowed to select RangeA1, I could lock this cell.

Such a script should be possible, no? I found this script as a possible starting point:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Cells(7, "K")) Is Nothing Then
        Cells(8, "K") = Cells(7, "K")
    End If
End Sub

What do you think? Could we work with this?

 

[My Aswer Is This]

Your script uses selection change.
This means if you select a certain cell it does not mean a change of a cell value.

You have a formula in A1 which looks at something not sure what the formula looks at
Like the formula could be; =G2+I5

 

[Retroshift]

The formula is

="TMS extract from "&'File sorting coding'!$D$10&" at "&'File sorting coding'!$D$12&"."

Could a script be used based on track changes, so as to export the previous cell history to another cell?