求助：基于公式单元格变动的VBA时间戳实现方案

Got it, let's fix this! The tricky part here is that Excel's default Worksheet_Change event only fires when you manually edit a cell's value—not when a formula's result updates (like your R column, which is built from other cells). So we need to use the Worksheet_Calculate event instead, paired with a variable to track previous values of the R column.

Step-by-Step Implementation #

First, open the code editor for Sheet2: right-click the Sheet2 tab > select View Code. Then paste the following code into the module that opens:

' Module-level variable to store the previous values of column R in Table1
Private prevRValues As Variant

' Initialize the previous values when Sheet2 is activated
Private Sub Worksheet_Activate()
    Dim table As ListObject
    Set table = Me.ListObjects("Table1")
    
    ' Only run if the table has data rows
    If Not table.DataBodyRange Is Nothing Then
        prevRValues = table.ListColumns("R").DataBodyRange.Value
    End If
End Sub

' Triggered every time the worksheet recalculates (including when formula results change)
Private Sub Worksheet_Calculate()
    Dim table As ListObject
    Dim currentRValues As Variant
    Dim rowIndex As Long
    
    Set table = Me.ListObjects("Table1")
    
    ' Exit if the table has no data
    If table.DataBodyRange Is Nothing Then Exit Sub
    
    ' Get the current values of column R
    currentRValues = table.ListColumns("R").DataBodyRange.Value
    
    ' Compare each row's current value to its previous value
    For rowIndex = LBound(currentRValues, 1) To UBound(currentRValues, 1)
        ' Handle null/empty cell cases to avoid errors
        Dim valueChanged As Boolean
        valueChanged = False
        
        If IsNull(currentRValues(rowIndex, 1)) Then
            valueChanged = Not IsNull(prevRValues(rowIndex, 1))
        ElseIf IsNull(prevRValues(rowIndex, 1)) Then
            valueChanged = True
        Else
            valueChanged = (currentRValues(rowIndex, 1) <> prevRValues(rowIndex, 1))
        End If
        
        ' If the value changed, add the timestamp to column H
        If valueChanged Then
            table.ListColumns("H").DataBodyRange(rowIndex, 1).Value = Now()
            ' Update the stored value to prevent duplicate timestamps on recalculation
            prevRValues(rowIndex, 1) = currentRValues(rowIndex, 1)
        End If
    Next rowIndex
End Sub

Key Details & Notes #

• Module-Level Variable: prevRValues keeps track of what the R column values were before the last calculation. This lets us spot exactly which rows changed when the worksheet recalculates.
• Worksheet_Activate: This initializes the prevRValues variable when you switch to Sheet2 (or open the file while on Sheet2) so we have a baseline to compare against.
• Handles Empty/Null Cells: The code checks for null values to avoid runtime errors when comparing empty cells.
• Table-Friendly: We use the table's column names ("R" and "H") instead of hardcoding column numbers. This means if you reorder columns in Table1 later, the code will still work.
• Prevents Duplicate Timestamps: After writing a timestamp, we update prevRValues with the new value—so if the worksheet recalculates again without changing R's value, we won't overwrite the timestamp.

How to Test It #

1. Make sure your R column in Table1 has the concatenation formula (e.g., =A2&" "&B2).
2. Switch to another sheet and back to Sheet2 to initialize the prevRValues variable.
3. Edit a cell in column A or B that feeds into R—you'll see the corresponding H cell update with the current date and time immediately.

内容的提问来源于stack exchange，提问作者nmergulh