Thread: when the data in a cell changes, automatically take a record of this change data

Hi All,

I am not sure how to go about creating a spreadsheet that does this:

I have a number much like a stock market index which is in cell A1 for instance,
and this data changes every second,

And I wish to create my spreadsheet to automatically make a record of this change value and populate it at the last available blank row, so ultimately, I have a full history of how this number changes during the course of time.


say

5450 in cell A1
a second later, it changes to 5430
and so I want to populate 5430 to A2

and then another second, it changes from 5430 to 5470
and so i want to populate 5470 to A3 and so forth....



does anyone know how to do it?


thanks

This is a sheet macro, right-click the sheet tab, select VIEW CODE and insert this code behind the sheet into the window that appears:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Application.EnableEvents = False
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("A1").Value
        Application.EnableEvents = True
    End If
End Sub

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

You had a followup question? Please post it here for the forum.

cartedor,

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.

Hello, how do I get this function to record the changes horizontally rather than vertically?