Record previous cell value

Retroshift

Board Regular
Joined
Sep 20, 2016
Messages
119
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,342
Office Version
  1. 2021
Platform
  1. Windows
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.
 
Upvote 0

Retroshift

Board Regular
Joined
Sep 20, 2016
Messages
119
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Upvote 0

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,342
Office Version
  1. 2021
Platform
  1. Windows
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"
 
Upvote 0

Retroshift

Board Regular
Joined
Sep 20, 2016
Messages
119
Office Version
  1. 2019
Platform
  1. Windows
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
 
Upvote 0

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,342
Office Version
  1. 2021
Platform
  1. Windows
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
 
Upvote 0

Retroshift

Board Regular
Joined
Sep 20, 2016
Messages
119
Office Version
  1. 2019
Platform
  1. Windows
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:
VBA Code:
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?
 
Upvote 0

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,342
Office Version
  1. 2021
Platform
  1. Windows
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
 
Upvote 0

Retroshift

Board Regular
Joined
Sep 20, 2016
Messages
119
Office Version
  1. 2019
Platform
  1. Windows
The formula is
Excel Formula:
="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?
 
Upvote 0

Forum statistics

Threads
1,186,382
Messages
5,957,537
Members
438,311
Latest member
OutLine86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top