Closed Thread
Results 1 to 13 of 13

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

  1. 09-19-2009, 09:41 AM #1
    cartedor
    cartedor is offline
    Registered User
    Join Date
    09-19-2009
    Location
    LONDON
    MS-Off Ver
    Excel 2003
    Posts
    9

    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
    Last edited by cartedor; 09-20-2009 at 07:42 AM.
    Reply With QuoteRegister To Reply
  2. 09-19-2009, 09:59 AM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    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:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 09-19-2009, 06:16 PM #3
    cartedor
    cartedor is offline
    Registered User
    Join Date
    09-19-2009
    Location
    LONDON
    MS-Off Ver
    Excel 2003
    Posts
    9

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

    thanks so much....

    i was almost there, but I made mistake with my target...so mine didnt work...
    thanks so so much!!!
    Reply With QuoteRegister To Reply
  4. 09-19-2009, 06:27 PM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    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)
    Reply With QuoteRegister To Reply
  5. 09-19-2009, 07:43 PM #5
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    You had a followup question? Please post it here for the forum.
    Reply With QuoteRegister To Reply
  6. 09-20-2009, 05:25 AM #6
    cartedor
    cartedor is offline
    Registered User
    Join Date
    09-19-2009
    Location
    LONDON
    MS-Off Ver
    Excel 2003
    Posts
    9

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

    Hi, Yes I do have a follow up question...

    i figured out how to make column A and Column B to repeat the above action.

    However, the tricky thing is that with the event change function...this works brilliantly if someone is to put a new number into Cell A1...
    but unfortunately if the data in Cell A1 is a formula, which generates a number, so its changing every second, the code does not do record it automatically.

    I have been looking at the Event Calculate, perhaps thats the function i need or a combination of the two function event change and event calculate.

    Alternatively....if i can get this code to do it every second or every other second to just make a record of the number in a certain cell - in this example its cell A1.

    Is there anything similar like that?

    Please advise, thanks.
    Reply With QuoteRegister To Reply
  7. 09-20-2009, 07:40 AM #7
    cartedor
    cartedor is offline
    Registered User
    Join Date
    09-19-2009
    Location
    LONDON
    MS-Off Ver
    Excel 2003
    Posts
    9

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

    i think they might be it....

    I swap column A and B around so i have time on column A and value on Column B...this seems to work...




    Private Sub Worksheet_Calculate()
    Worksheet_Change Range("B1")
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B1")) Is Nothing Then
    Application.EnableEvents = False
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("A1").Value
    Application.EnableEvents = True
    Application.EnableEvents = False
    Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("B1").Value
    Application.EnableEvents = True
    End If
    End Sub
    Reply With QuoteRegister To Reply
  8. 09-20-2009, 01:08 PM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

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

    cartedor,

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

    Thanks.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  9. 11-18-2011, 06:03 AM #9
    whitlos
    whitlos is offline
    Registered User
    Join Date
    05-06-2011
    Location
    Bognor
    MS-Off Ver
    Excel 2003
    Posts
    1

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

    Hello, how do I get this function to record the changes horizontally rather than vertically?
    Last edited by whitlos; 11-18-2011 at 06:20 AM.
    Reply With QuoteRegister To Reply
  10. 12-13-2012, 01:27 AM #10
    michael.marchand
    michael.marchand is offline
    Registered User
    Join Date
    12-15-2011
    Location
    colorado, america
    MS-Off Ver
    Excel 2007
    Posts
    2

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

    Or to send to a different sheet?
    Reply With QuoteRegister To Reply
  11. 12-13-2012, 01:40 AM #11
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    Michael,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  12. 06-07-2018, 03:50 PM #12
    ack1128
    ack1128 is offline
    Registered User
    Join Date
    06-07-2018
    Location
    Chattanooga Tennessee
    MS-Off Ver
    Excel 2010
    Posts
    1

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

    I too want to track changes to my A1 cell and have a play by play analisis of what A1 equals when it changes. put in that code but I get no response when A1 does change. Nothing happens.
    Reply With QuoteRegister To Reply
  13. 06-07-2018, 03:53 PM #13
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

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

    ack1128 welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
Closed Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1