Prevent recalculation of NOW() function in Col B that depends on values entered in Col A.

Posted on 2011-04-30
Last Modified: 2012-05-11
Values are scanned into cells in Col A and the adjacent cell in Col B has the following calculation.
CELL B2:    =If(A2="","",NOW())
CELL B3     =If(A3="","",NOW())
CELL B4     =If(A4="","",NOW())

The intial calc works OK for each entry in Col A, but the previous calc in Col B recalculates to the
current NOW() value.
I need to keep the original value calulated, for example, in Cell B2 that was calculated when a
value was entered into Cell A2.
Currently, the value in Cell B2 is recalculting when (ie, all previious calculated values in Col B
are recalulating) when subsequent values are scanned into sunsequent cells in Col A such as
Cell A3, Cell A$, etc.

Question by:amcgaughey1
    LVL 76

    Expert Comment

    You would need to assign the value of NOW() to a variable prior to processing this.
    is this an Excel VBA/Macro you are working on?

    Author Comment

    This is not an Excel VBA?Macro.  Input into cells in Col A are via barcode scan.  As each scan is processed the focus shifts to the next cell below.  Since the cell thta just received the input has a value it causes the contiguous in Col B to calculate witht he current date/time which is what is the desired datra capture.  However, all the other cells in Col B wiith the "IF" calculation also recalculate to the same date/time as that caused by the most recent data entry.  This is not what I need. I need to retain the date/time that is originallyncaltaed with its associated data entry in Col A.
    If I am going to use an Excel VBA\Macro what event would I use to triiger the associated calaculation data capture?
    LVL 67

    Expert Comment

    Just add another IF, checking whether there is already an value in the B cells:

    B2: = If(isempty(A2),"", If(isempty(B2), NOW(), B2))
    LVL 22

    Accepted Solution

    In VBA, you'd probably need something that adds the current time when a particular cell changes.

    Assuming you are on Sheet1, use Alt+F11 to get into the VBA area then double-click on Sheet1 in the project panel (top, left corner, may need Ctrl+R to open it.)
    In the window that opens to the right, enter this:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Const TimeColumn As Integer = 2
        Const ValueColumn As Integer = 1
        Dim iRow As Integer
        If Target.Count = 1 Then
            iRow = Target.Row
            If Cells(iRow, TimeColumn).Value = "" And Cells(iRow, ValueColumn).Value <> "" Then
                Cells(iRow, TimeColumn).Value = Now
            End If
        End If
    End Sub

    Open in new window

    Now every time a value changes (except when a value is cleared) the current time will be put in the time column.
    You may need to adjust the column.  I inferred that the data is column 1 and the time is supposed to be in column 2.
    You should also remove any formulas from the time column.
    LVL 4

    Expert Comment

    If I recall, a barcode input won't trigger a Change event, but you can use a calculate event:

    Private Sub Worksheet_Calculate()
        Dim i As Long
            i = Cells(Rows.Count, "A").End(xlUp).Row
            Cells(i, "B").Value = Now
    End Sub

    Open in new window


    Author Closing Comment

    Perfect. Thank you!

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    This collection of functions covers all the normal rounding methods of just about any numeric value.
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now