如果活动单元格符合条件，如何删除右边两个单元格的内容

Question

我已经编写了以下代码，如果活动单元格= 'yes'或'no'，则在单元格右侧输入日期。这部分代码正在工作，但是当活动单元格不符合标准时，我希望它清除右边两个单元格的内容。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will cause an input
'date and time in next 2 cells to the right when active cell is changed.
Set KeyCells = ActiveSheet.ListObjects("VW_P1_P2").ListColumns("C1 Made Contact?").Range
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
If Target = "Yes" Or Target = "No" Then
ActiveCell.Offset(-1, 1).Value = Format(Now, "mm/dd/yyyy")
ActiveCell.Offset(-1, 2).Value = Format(Now, "hh:mm")
Else
ActiveCell.Offset(-1, 1).ClearContents
ActiveCell.Offset(-1, 2).ClearContents
End If
End If
End Sub

Answer 1

几个问题/改进:

• 使用Me来引用父工作表，而不是ActiveSheet。
• 避免使用ActiveCell，而使用Target来表示已更改的单元格。
• Range(Target.Address)是多余的。就用Target吧。
• 如果Target是多单元格范围，则不能将其与"Yes"或"No"进行比较，因此使用循环。
• 您正在以编程方式更改工作表，因此最佳实践是暂时禁用事件，并在结束时重新启用它们。
• 我建议使用.ListColumns("C1 Made Contact?").DataBodyRange代替.ListColumns("C1 Made Contact?").Range。这将排除列标头C1 Made Contact。
• 可以用Date代替Format(Now, "mm/dd/yyyy")。

Private Sub Worksheet_Change(ByVal Target As Range)
' The variable KeyCells contains the cells that will cause an input
'date and time in next 2 cells to the right when active cell is changed.
Dim KeyCells As Range
Set KeyCells = Me.ListObjects("VW_P1_P2").ListColumns("C1 Made Contact?").DataBodyRange
Dim rng As Range
Set rng = Application.Intersect(KeyCells, Target)
If Not rng Is Nothing Then
On Error GoTo SafeExit
Application.EnableEvents = False
Dim cell As Range
For Each cell in rng
If cell.Value = "Yes" Or cell.Value = "No" Then
cell.Offset(-1, 1).Value = Format(Now, "mm/dd/yyyy") ' or just Date
cell.Offset(-1, 2).Value = Format(Now, "hh:mm")
Else
cell.Offset(-1, 1).ClearContents
cell.Offset(-1, 2).ClearContents
End If
Next
End If
SafeExit:
Application.EnableEvents = True
End Sub

编辑:

如果KeyCells是表中的多列，那么你可以使用Union:

With Me.ListObjects("VW_P1_P2")
Dim KeyCells As Range
Set KeyCells = Union(.ListColumns("C1 Made Contact?").DataBodyRange, _
.ListColumns("C2 Made Contact?").DataBodyRange, _
.ListColumns("C3 Made Contact?").DataBodyRange)
End With