excel - 如果另一个单元格变为空白，则清除单元格内容

Question

我有一个 Excel 工作表，如果在 A 列的单元格中输入一个值，则 B 列中该值旁边的单元格会自动生成日期和时间。我遇到的问题是我想检查一下 A 列中的值是否为空，即“”，那么日期和时间也被清除。我已经弄清楚如何添加日期和时间，而不是如何添加对单元格值的检查以查看它是否为空白。下面是代码和示例；

示例；

A4 a change is made, the current date and time is entered into B4 
A8 a change is made, the current date and time is entered into B8
A4 the user clears the cell (presses delete on their keyboard), B4 is cleared too.
A4 the user enters "hello world", the current date and time is entered again

代码；

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
xCellColumn = 2
xTimeColumn = 5
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
    If xCol = xCellColumn Then
       Cells(xRow, xTimeColumn) = Now()
    End If
End If
End Sub

谢谢

Answer 1

这仅适用于列 A 和列 B:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    If Target = vbNullString Then
        Target.Offset(0, 1) = vbNullString
    Else
        Target.Offset(0, 1) = Now
    End If

    Application.EnableEvents = True

End Sub

如果您想让它适用于任意 2 列，请删除此行:

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

命令 Application.EnableEvents = True 用于确保在 Sub 更改单元格后不会调用 _Change 事件。