excel - 如何解决这个 "ambiguous name detected Worksheet_Change"？

Question

我正在尝试添加两个类似的宏来执行相同的操作(在两个不同的列中)，但是当我让它运行时，它会显示:“检测到 Worksheet_Change 的名称不明确”。 (您可以在下面找到代码)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("Cost_to_date"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "mmm dd, yyyy"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("Last_update"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "mmm dd, yyyy"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

如果我尝试执行以下操作以使其更小，则 Cost_to_date 和 Last_update 之间的所有列都会自动添加日期...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("Cost_to_date","Last_update"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "mmm dd, yyyy"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

他们应该只更改名为(迄今为止的成本和上次更新)的两列右侧的列，并单独工作。
请让我知道我在这里缺少什么。

Answer 1

你可以试试下面的吗？这似乎对我有用，所以只是想检查一下你的情况。当我一次突出显示并清除整个列时，这确实让我崩溃了，仅供引用。

我相信你需要检查 Intersect每个范围单独输入宏，当其中一个为 TRUE 时输入宏.从那里我循环通过Target并相应地进行检查，例如

目标不为空

目标列正确

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xCell As Range, x As Long, y As Long
x = Range("Last_Update").Column
y = Range("Cost_to_date").Column

If Not Intersect(Range("Last_Update"), Target) Is Nothing Or Not Intersect(Range("Cost_to_date"), Target) Is Nothing Then
    Application.EnableEvents = False
    On Error GoTo SafeExit
    
    For Each xCell In Target
        If xCell.Column = x Or xCell.Column = y Then
            If xCell <> "" Then
                xCell.Offset(, 1) = Now
            Else
                xCell.Offset(, 1).ClearContents
            End If
        End If
    Next xCell
    
    Application.EnableEvents = True
    Target.Offset(, 1).NumberFormat = "mmm dd, yyyy"
    
End If
Exit Sub

SafeExit:

Application.EnableEvents = True
MsgBox "Error Occured", vbCritical

End Sub