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

Require solution in setting formula based on change in cell



I got the solution on how to record last value and second last value of same cell. Further, want the result in respective cells for which finding difficult to set formula based on requirement in file "Record last 2 values changed via data link v0 b".

There requirement is the same with refer to the earlier solution provided but unable to reset the formula based on new cell in excel. 

When there is change in Current Value 1 and Current Value 2, accordingly there should be change in Last Value1, Previous value1 and in Last Value 2 and Previous value2.

Attaching excel sheet.

Please help with solution.




Selected Answer


Firstly you can't set a single (double) number to be the values of a range, as you attempted in your line:

ThisVal = Sheet2.Range("E2:F200").Value

Secondly, the arrangement of your sets of current and last/previous values (within the worksheet Report Sheet and compared to Intermediate) makes it tricky to move values and rules out the Offset/Resize method used in my recent answer how to record last value and second last value of same cell (other users should read/ try that first, please).

Attached is a revised .xlsx file (you modified from the version in my previous answer but kept the file name the same- bad!) plus a revised .xlsm file - you need to have both files up for the demo to work.

In the Intermediate sheet, I'm assuming your values have names and you can add then in column A (and the Report Sheet cells in A will reflect that via a simple formula). The macro only looks as the range upto the last item name in that column. It assumes too that the rows in the  .xlsx file match those in the two sheets of the .xlsm file.

The macro to do what you want is uses two nested loops run through rows and columns of Intermediate. It's as follows, with comments and where the bits in bold need thinking about - they calculate which column the last/current/new values shift to:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

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

Dim ThisVal As Double
Dim LstRw As Long, Row As Long, Col As Long

' get last populated row of column A of Intermediate
LstRw = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row

' loop through and columns of Intermediate
For Col = 5 To 6
    'Loop through populated rows
    For Row = 2 To LstRw
        ' capture each remote value in E:F
        ThisVal = Sheet2.Cells(Row, Col).Value
        ' look at corresponding cell C:D in Report Sheet
        With Sheet1
            'check if value changed and modify Report Sheet
                If ThisVal <> .Cells(Row, Col - 2).Value Then
                    'shift last value to previous
                    .Cells(Row, 2 * Col).Value = .Cells(Row, 2 * Col - 1).Value
                    'shift current value to last
                    .Cells(Row, 2 * Col - 1).Value = .Cells(Row, Col - 2).Value
                    'write new value to current
                    .Cells(Row, Col - 2).Value = ThisVal
                End If
        End With
    Next Row
Next Col

End Sub

Clue: Col takes on only two values- 5 or 6 and your last / previous sets are in columns 9/10 and 11/12

Don't forget to have both new files visible on screen and change the values in the file Remote data link with changing value v0 b.xlsx.

Hope this works for you.


Answer the Question

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