Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

how to record last value and second last value of same cell


I am looking for how to record last value and second last value of same cell in excel through vba For example, when one cell value is 100 than the same cell value change to 50 than the last cell value is 50 and second last cell value is 100.

want to record both 50 as last change value and 100 as second last change value.

Please find attached excel sheet for your reference.

Can you please help for solution.




Hi Sunil. Long time no see.

Please edit your qyestion to add a representative Excel file and say which celk(s) you want to track. (From experience I recall that you often added aspects to the question after an answer has been given.) 
John_Ru (rep: 6212) Apr 18, '23 at 1:40 am
Sunil. I got an Alert saying your question had been updated but I don't see a file or an answer to my question about cell(s) above. Waiting for you again 
John_Ru (rep: 6212) Apr 18, '23 at 2:33 am
Hi John,
Thanks for remembering.
Attached the excel file for your ready reference.
SunilA (rep: 58) Apr 18, '23 at 8:03 am
Sunil. I've seen your file but I'm not clear what you want to do- is it simply to write the last value to B2 (after A2 is changed) and then B2 to C2 (as second last value)? Or to write comments like in column E (witrh timing)?  Just one cell to monitor (and it is not updated remotely)?
John_Ru (rep: 6212) Apr 18, '23 at 8:55 am
Yes you are right, the value to write the last value to B2 (after A2 is changed) and then B2 to C2 (as second last value)
For example,
Lets say, At 10:00 AM the value in A2 is 100
than at 10:01 AM the value in A2 is 50
So, In B2, the last value to be capture of 50 and in C2 the second last value to be capture of 100 (the value before the last value). 
The cell value in A2 is updating remotely (Automatically). So, B2 and C2 to capture accordingly.
SunilA (rep: 58) Apr 18, '23 at 11:03 am
Thanks Sunil but in future you MUST put this kind of detail in your question- other contributors (mainly Willie) will answer your question as it is written then be surprised (/annoyed) when you add other aspects. I'll try to look at this later today.
John_Ru (rep: 6212) Apr 18, '23 at 11:32 am
Add to Discussion


Selected Answer


You explained in the Discvussion (below your question) that you want to track a value which is chnaged remotely.

As in a previous solution for you, I've created a .xlsx file (which needs to be open) and cell B2 (yellow) is the value which might changed by you entering a new value.

In the .xlsm file, the sheet "Intermediate" links to that cell since cell A2 has the formula:

='[Remote data link with changing value v0_a.xlsx]Sheet1'!$B$2

With both files open. type a new value in yellow cell B2 of Remote data link with changing value v0_a.xlsx

That triggers this event macro (at the Workbook level) in the .xlsm file and records the new value and time (if changed), moving previous values too:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

If Sh.Name <> "Intermediate" Then Exit Sub

Dim ThisVal As Double
' capture remote value
ThisVal = Sheet2.Range("A2").Value

' look as cell A2 in Report Sheet
With Sheet1.Range("A2")
    'check if value changed and modify Report Sheet
        If ThisVal <> .Value Then
            'shift A2:B3 to B2:C3
            .Offset(0, 1).Resize(2, 2).Value = .Resize(2, 2).Value
            ' write new value and time(below)
            .Value = ThisVal
            .Offset(1, 0).Value = Now
        End If
End With

End Sub

You can hide the sheet Intermediate if you like.

Hope this works for you.



The file is not working. The source, start time and end time is having issue.
Please help with correct sheet.
SunilA (rep: 58) Apr 19, '23 at 2:26 am

Don't know what you mean by "start time and end time" but the . xlsm file is working for me. E.g. It was sent with the Current value 100 yesterday. I changed the value in the .xlsx twice (to 75 then later 49) and the Report sheet now reads (my local time):

Current value/time	Last value/time	Previous value/time
              49                      	75              	100
19/04/2023 08:25	19/04/2023 08:23	18/04/2023 20:02

Have you got both workbooks open but changing values in only the yellw cell of the .xlsx? Have you renamed the .xlsx or changed the formula in the Intermediate sheet of .xlsm?
John_Ru (rep: 6212) Apr 19, '23 at 3:34 am
The excel is working. 
Thanks John.
Are you also resolve query regarding Python.
I have query on Python. Can you please able guide me for the same.
Thanks for your support Always.
SunilA (rep: 58) Apr 19, '23 at 4:33 am
Thanks for selecting my Answer, Sunil. I don't know Python, sorry 
John_Ru (rep: 6212) Apr 19, '23 at 6:19 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login