excel - 如果单元格已更新，则在下一个空白行中写入单元格值和时间戳

Question

我正在尝试更新带有时间戳的列和带有更新的另一列(来自特定单元格)。

期望的行为:
A2 已写入 xy。该更改触发了一个宏，它将时间戳放在第 2 行的 C 列中，并将更新放在第 2 行的 D 列中。
如果在 A2 中进行了新的更新:如果 C2 不为空，则跳转到 C3 并放置时间戳并将更新放在 D3 等等。

不幸的是，它将第一个更新时间戳和更新放入列，但如果我再次更新，它不会跳转并将更新放在那里。

Error message and Excel macro
Excel sheet which I try to update.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCellColumn As Integer
    Dim xCellRow As Integer
    Dim xTimeColumn As Integer
    Dim xTimeRow As Integer
    Dim xUpdateColumn As Integer
    Dim xUpdateRow As Integer
    Dim xRow, xCol As Integer
    xCellColumn = 2
    xCellRow = 10
    xTimeColumn = 6
    xTimeRow = 2
    xUpdateColumn = 7
    xUpdateRow = 2
    i = 2
    xCol = Target.Column
    xRow = Target.Row
    If Target.Text <> "" Then
       If xCol = xCellColumn Then
            If xRow = xCellRow Then
                Do While Range("Munka1").Cells(i, xTimeColumn).Value <> ""
                  i = i + 1
                Loop
                Cells(i, xTimeColumn) = Now()
                Cells(i, xUpdateColumn) = Target.Value
            End If
        End If
    End If
End Sub

Answer 1

请试试这个:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCellColumn As Long
    Dim xCellRow As Long
    Dim xTimeColumn As Long
    Dim xUpdateColumn As Long
    Dim SupervisedArea As Range
    Dim i As Integer

    xCellColumn = 2
    xCellRow = 10
    xTimeColumn = 6
    xUpdateColumn = 7

    If Target.Text <> "" Then
        ' If any changed value in the whole column should generate a new data pair:
        'Set SupervisedArea = Intersect(Target, Me.Columns(xCellColumn))
        ' If only one cell should be supervised:
        Set SupervisedArea = Intersect(Target, Me.Cells(xCellRow, xCellColumn))
        If Not SupervisedArea Is Nothing Then
            i = 2
            Do While Me.Cells(i, xTimeColumn).Value <> ""
              i = i + 1
            Loop
            Application.EnableEvents = False
            Me.Cells(i, xTimeColumn) = Now()
            Me.Cells(i, xUpdateColumn) = Target.Value
            Application.EnableEvents = True
        End If
    End If
End Sub