vba - Worksheet_change 似乎没有触发

Question

我有一个宏启动并运行，它对数据透视表进行排序，从表中复制一系列单元格，然后将它们粘贴到第二张表中。老实说，就我现在使用 VBA 的情况而言，我对这一壮举感到非常满意。然而，我还有更多想要它做的事情。

我想要发生的是:我将数据粘贴到 A 列的第一个空单元格中的宏。发生这种情况时，我希望宏输入今天的日期(最好以使其永久不变且不会更改为明天的方式)明天的日期)在 C 列的同一行中，并将文本“IV020”放入 D 列。

在 Sheet9 我有以下代码(主要取自这里的帖子):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim aCell As Range

    On Error GoTo Whoa

    Application.EnableEvents = False

    If Not Intersect(Target, Columns(1)) Is Nothing Then
        If Not Target.Columns.Count > 1 Then
            For Each aCell In Target
                If aCell.Value <> "" And aCell.Offset(0, 2).NumberFormat = "" Then
                    aCell.Offset(0, 2).Value = "=TODAY()"
                    aCell.Offset(0, 3).Value = "IV020"
                End If
            Next
        Else
            MsgBox "Please paste in 1 Column"
        End If
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

然而，当将内容粘贴或手动输入到 A 列时，什么也没有发生。

Answer 1

您不能拥有 Range.NumberFormat property那是一个零长度的字符串。即使您尝试手动放入，它也会自行重置为 一般 .

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Columns(1)) Is Nothing Then
        'don't do things until you have to
        On Error GoTo Whoa
        Application.EnableEvents = False
        Dim aCell As Range
        'this processes all of the cells that were changes in column A
        For Each aCell In Intersect(Target, Columns(1))
            'If aCell.Value <> "" And aCell.Offset(0, 2).NumberFormat = "" Then
            If aCell.Value <> "" Then
                aCell.Offset(0, 2).Value = Date   'possibly Now but likely not "=TODAY()"
                aCell.Offset(0, 3).Value = "IV020"
            End If
        Next
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

我做了一些小改动；您必须决定要对 .NumberFormat 问题做什么。