问如果活动单元格符合标准，如何删除右边的两个单元格的内容
EN

Stack Overflow用户

提问于 2022-11-10 07:17:33

回答 1查看 35关注 0票数 2

如果活动单元格= '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

excel

vba

excel-2016

DNS解析特惠

DNS解析提供智能解析、流量调度、安全防护等服务

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-11-10 07:27:51

若干问题/改进：

ActiveSheet.

Avoid

使用Me来引用父工作表，而不是使用ActiveCell来引用

，而使用Target来引用更改的

是多余的。只需使用Target.

If Target是一个多单元范围，您不能将其与"Yes"或"No"进行比较，所以请使用循环。

您是通过编程方式更改表的，所以最好的做法是暂时禁用事件，并在结束时重新启用它们。

我建议使用.ListColumns("C1 Made Contact?").DataBodyRange而不是.ListColumns("C1 Made Contact?").Range。这将排除列标题C1 Made Contact.

Instead of Format(Now, "mm/dd/yyyy")，您可以只使用Date.

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