1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Record dynamic cell value with date/time stamp and then use lookup

Discussion in 'VBA Macros' started by Sam Longstaff, Jan 10, 2019.

  1. Sam Longstaff

    Sam Longstaff New Member

    Messages:
    23
    I have a worksheet that has a dynamic cell (A1) with a constantly changing value. I want to record a history of this value and the time and date that it has changed. I have found some VBA solutions online that can do a time/date stamp or a list history of the value but not a VBA solution that does both. So ideally I would have:

    A1 - Dynamic value
    B1 - First historic value of A1, C1 - Date/time stamp
    B2 - Second historic value of A1, C2 - date/time stamp
    and so on.

    Once I have this list I would then wish to be able to lookup the list so that I could get the historic value of A1 at the end of each month and record this so that it can feed in to a performance dashboard. I did have an array formula solution that was initially working but this formula was made using indirect and then became volatile.

    I hope someone can help. Thank you.
  2. charlesdh

    charlesdh Member

    Messages:
    69
    Hi,

    You can use a worksheet change event to record the data each time the value is changed. You should be able to search this forum that may have the answer you seek.
  3. charlesdh

    charlesdh Member

    Messages:
    69
    Hi,
    Here' a code that you can look at.
    In the worksheet for the amount you will need a header. Also a header for column C.
    Copy code to the worksheet that you are using.

    Code (vb):

    Private Sub Worksheet_Change(ByVal target As Excel.Range)
    Dim lrow As Long
    If target.Address <> "$A$2" Then Exit Sub
    '' get last row in column ''
    lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Row + 1''' change sheet1 to your sheet
    Application.EnableEvents = False
    Sheets("Sheet1").Range("C" & lrow).Value = Format(Now(), "yyyy-MM-dd hh:mm:ss")
    Application.EnableEvents = True
    End Sub
     
    Last edited: Jan 10, 2019
  4. Sam Longstaff

    Sam Longstaff New Member

    Messages:
    23
    Thanks for your response Charles. It is recording a time stamp but not a historical record of the dynamic cell value with a timestamp next to it. Any ideas?
  5. charlesdh

    charlesdh Member

    Messages:
    69
    Hi,
    I'll be out today.
    If possible post a sample.
    Else another member may help.

Share This Page