How to capture a cell value in Excel when a cell updated

Question

I have a cell in Excel that gets updated on basis of RAND() function. Each time I update the cell, I want to capture the previous values in a separate column. So that I can compute mean later on.

How to achieve this??

You could look into `Worksheet_Calculate()` and go from there. — JvdV, Feb 28 '19 at 11:57
Why go through the cell value? Why not write the rnd value into a variable as well? — , Feb 28 '19 at 12:11
Take a look at this [SO Questions](https://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba) — GMalc, Feb 28 '19 at 13:09

score 0 · Answer 1 · answered Feb 28 '19 at 13:03

0

Copy below into Worksheet module and change "A1" cell:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Intersect(Target, Range("A1")) Is Nothing) Then
    i = i + 1
    Worksheets("Sheet1").Cells(i, 2).Value = Worksheets("Sheet1").Cells(1, 1).Value
    End If
End Sub

answered Feb 28 '19 at 13:03

Sergey

87
8

Recalculation of a function doesn't trigger a Worksheet_Change event. – JvdV Feb 28 '19 at 14:26

How to capture a cell value in Excel when a cell updated

1 Answers1