Capturing changes in reference cell value

[kissthechef]

Hi,

I need the solution for the following:

1. Currently I have a randbetween() formula in cell B2.

2. Cell A1 = B2 (Every time the value in B2 changes so does the value in A1)

Instead,
I want that every change in value in cell B2 is captured in progressive cells.

For example:

First iteration in cell B2, value is entered in A1.
Second change in value in B2, value is entered in A2, while value in A1 remains unchanged and so on and so forth

Can any one help?

 

[Sal Paradise]

You need to write a macro.

You can use the Worksheet Calculate event to do it though:

Private Sub Worksheet_Calculate()
    Dim intLastRow As Integer
    
    Application.EnableEvents = False
    
    varCalculation = Application.Calculation
    Application.Calculation = xlCalculationManual
        
    With Worksheets("Sheet1")
        intLastRow = .Range("A65535").End(xlUp).Row
        .Range("A1").Offset(intLastRow).Value = .Range("B2").Value
    End With
    
    Application.Calculation = varCalculation
    Application.EnableEvents = True
End Sub