Question

我有以下法典,用于监测特定细胞的变化。准则参考指标。但是,如果在表格中增加一栏,则我会使用一栏名称,因此该代码不停。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 6 Then
          Target.Worksheet.Cells(Target.Row, 15) = Format(Now(), "mmmm d, yyyy at h:mm:ss")
    End If
    If Target.Column = 8 Then
          Target.Worksheet.Cells(Target.Row, 15) = Format(Now(), "mmmm d, yyyy at h:mm:ss")
    End If
End Sub

My table is "AV_Database" Column 6 is "DLR" Column 8 is "MSRP" Column 15 is "Last Price Update"

守则在发挥作用,但在我插入一栏或修改命令时便被打破。我要通过提及一栏名使这更加有力。

博努斯问题,我还有另一个小组,我想监测一下,以便启动时间序列,但上面的代码没有奏效。我认为,这是因为囚室是一种由其他囚室决定的公式,而不是价值。是否也能够使这项工作发挥作用? 这将是第13栏或“德国航天中心CDN”一栏。

事先感谢你们的所有帮助。

Answer 1

Microsoft documentation:

Application。 sect(Excel)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
      The list of headers on trigger Cols, modify as needed
    Const KEY_COL = "DLR|MSRP|$ DLR CDN"
    Const UPDATE_COL = "Last Price Update"
    Const TAB_NAME = "AV_Database"
    With Target
          validate changed cell is single
        If .CountLarge > 1 Then Exit Sub
          check change is a table (ListObject)
        Dim oTab As ListObject: Set oTab = .ListObject
        If oTab Is Nothing Then Exit Sub
          check table name
        If UCase(oTab.Name) <> UCase(TAB_NAME) Then Exit Sub
          check change is not on table header
        Dim r As Range: Set r = Application.Intersect(oTab.HeaderRowRange, .Cells)
        If Not r Is Nothing Then Exit Sub
          get the header of changed column
        Set r = Application.Intersect(oTab.HeaderRowRange, .EntireColumn)
          check the change is on trigger cols
        If InStr(1, KEY_COL, r.Value) > 0 Then
            Dim updateCell As Range, iCol
              get the index of changed col
            iCol = Application.Match(UPDATE_COL, oTab.HeaderRowRange, 0)
            If IsError(iCol) Then Exit Sub
              update the cell with timestampe
            Set updateCell = Application.Intersect(oTab.ListColumns(iCol).Range, .EntireRow)
            Application.EnableEvents = False
            updateCell.Value = Format(Now(), "mmmm d, yyyy at h:mm:ss")
            Application.EnableEvents = True
        End If
    End With
End Sub

友情链接