首页 文章

当另一个单元格的值发生变化时，单元格中的自动日期更新（由公式计算）

提问于
浏览 次
5

我在C2中有一个公式，比如 =A2+B2 . 每当C2改变值（实际值，而不是公式）时，我希望在D2中更新当前日期和时间 .

我已经尝试了很多VBA代码和技巧，如果在C2中输入公式，它们都不起作用 . 但是如果我在C2中手动输入值，则会根据需要更新日期和时间 . 这当然是因为输入/改变了实际值 - 可以这么说公式保持不变 .

Question: 当C2中的公式结果发生变化时，是否可以创建更新D2的VBA代码（或其他内容）？

如果可能的话，我需要将它激活为单元格C2：C30（D2：D30表示日期时间）

使用Excel 2010 .

excel vba date formula

4 回答

  • 0

    您可以通过用户定义函数（VBA宏函数）填充依赖单元格（D2），该函数将C2-Cell的值作为输入参数，将当前日期作为输出返回 .

    将C2作为D2中UDF的输入参数告诉Excel每次C2更改时都需要重新评估D2（即如果为工作簿打开公式的自动计算） .

    EDIT:

    这是一些代码：

    对于UDF：

    Public Function UDF_Date(ByVal data) As Date

        UDF_Date = Now()

    End Function

    作为D2中的公式：

    =UDF_Date(C2)

    您必须为D2-Cell提供日期时间格式，否则它将显示日期值的数字表示 .

    如果将C2参考保留在D2公式相对值中，则可以通过拖动来扩展所需范围内的公式 .

    Note: 这仍然可能不是理想的解决方案，因为每次Excel重新计算工作簿时，D2中的日期都将重置为当前值 . 为了使D2仅反映C2最后一次更改，必须对C2的过去值进行某种跟踪 . 这可以例如在UDF中通过提供输入参数的值的地址，将输入参数存储在隐藏的表中，并且每次调用UDF时将它们与先前的值进行比较来实现 .

    Addendum:

    以下是UDF的示例实现，它跟踪单元格值的更改并返回检测到上次更改时的日期时间 . 使用时请注意：

    • UDF的用法与上述相同 .

    • UDF仅适用于单个单元格输入范围 .

    • 通过存储单元格的最后一个值以及在工作簿的文档属性中检测到更改的日期时间来跟踪单元格值 . 如果公式用于大型数据集，则文件的大小可能会大大增加，因为存储要求增加的公式跟踪的每个单元格（上次更改的单元格日期的最后一个值） . 此外，Excel可能无法处理非常大量的文档属性和代码可能会在某一点制动 .

    • 如果更改了工作表的名称，则其中包含的单元格的所有跟踪信息都将丢失 .

    • 代码可能会为单元格值制动，对于该单元格，转换为字符串是非确定性的 .

    • 以下代码为 not tested ，仅应视为概念证明 . Use it at your own risk .

    Public Function UDF_Date(ByVal inData As Range) As Date

    Dim wb As Workbook
    Dim dProps As DocumentProperties
    Dim pValue As DocumentProperty
    Dim pDate As DocumentProperty
    Dim sName As String
    Dim sNameDate As String

    Dim bDate As Boolean
    Dim bValue As Boolean
    Dim bChanged As Boolean

    bDate = True
    bValue = True

    bChanged = False


    Dim sVal As String
    Dim dDate As Date

    sName = inData.Address & "_" & inData.Worksheet.Name
    sNameDate = sName & "_dat"

    sVal = CStr(inData.Value)
    dDate = Now()

    Set wb = inData.Worksheet.Parent

    Set dProps = wb.CustomDocumentProperties

On Error Resume Next

    Set pValue = dProps.Item(sName)

    If Err.Number <> 0 Then
        bValue = False
        Err.Clear
    End If

On Error GoTo 0

    If Not bValue Then
        bChanged = True
        Set pValue = dProps.Add(sName, False, msoPropertyTypeString, sVal)
    Else
        bChanged = pValue.Value <> sVal
        If bChanged Then
            pValue.Value = sVal
        End If
    End If

On Error Resume Next

    Set pDate = dProps.Item(sNameDate)

    If Err.Number <> 0 Then
        bDate = False
        Err.Clear
    End If

On Error GoTo 0

    If Not bDate Then
        Set pDate = dProps.Add(sNameDate, False, msoPropertyTypeDate, dDate)
    End If

    If bChanged Then
        pDate.Value = dDate
    Else
        dDate = pDate.Value
    End If


    UDF_Date = dDate
 End Function
    回复于
  • 4

    以范围为条件插入日期 .

    这具有不改变日期的优点，除非单元格的内容被改变，并且它在C2：C2的范围内，即使单张被关闭并保存，除非相邻单元格改变，否则它不会重新计算 .

    改编自this tip和@Paul S回答

    Private Sub Worksheet_Change(ByVal Target As Range)
 Dim R1 As Range
 Dim R2 As Range
 Dim InRange As Boolean
    Set R1 = Range(Target.Address)
    Set R2 = Range("C2:C20")
    Set InterSectRange = Application.Intersect(R1, R2)

  InRange = Not InterSectRange Is Nothing
     Set InterSectRange = Nothing
   If InRange = True Then
     R1.Offset(0, 1).Value = Now()
   End If
     Set R1 = Nothing
     Set R2 = Nothing
 End Sub
    回复于
  • 0
    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$C$2" Then

        ActiveSheet.Range("D2").Value = Now()

    End If

End Sub
    回复于
  • 10

    最简单的方法是添加 =IF(B3="","Not Allocated",Now()) 并将列的格式更改为所需的日期和时间格式 . 但是，如果编辑了B列，则需要更新的相应列的日期和时间将自动更新所有列，因为不检查旧值 . 但如果获得当前时间可以很好，这可以很容易地使用 .

    回复于

相关问题