EXCEL VBA，更改单元格组和Worksheet_Change事件

Question

我怀疑这并不是那么复杂，但我没有多少运气为Google找到合适的术语...所以我来找专家！

所以我试图实现一个Worksheet_Change事件。这非常简单，我基本上只想做以下事情：

如果C列中的值发生变化，并且D中的值（在该行中）具有特定的格式（NumberFormat =“$ 0.00”），则E列（在该行）是这两个值的乘积。简单。实际上，我只想要在E列中使用公式的VBA等价物。这个代码我使用：

Private Sub Worksheet_Change(ByVal Target As Range) 

If Target.Column = 3 And Target.Value <> "" Then 
    If Target.Offset(0, 1).NumberFormat = "$ 0.00" Then 
     Target.Offset(0, 2).Value = Target.Value * Target.Offset(0, 1).Value 
     End If 
     End If 
end sub   

我的问题是雨后春笋般冒出来，当我尝试在多个值粘贴到c柱的多行。即我将一列数据（> 1行）复制到C中，并且出现类型不匹配错误。我会做出巨大的飞跃，它不能很好地处理这个问题，因为“目标”意图是一个单一的单元而不是一个组。我希望有一种简单的方法来处理这个问题，每次单元格上的单元格发生变化时都不会出现一些疯狂的循环。

在此先感谢！

Answer 1

这是你正在尝试？

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

    On Error GoTo Whoa 

    Application.EnableEvents = False 

    If Not Intersect(Target, Columns(3)) Is Nothing Then 
     For Each aCell In Target 
      If aCell.Value <> "" And aCell.Offset(0, 1).NumberFormat = "$ 0.00" Then 
       aCell.Offset(0, 2).Value = aCell.Value * aCell.Offset(0, 1).Value 
      End If 
     Next 
    End If 

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

你也可能需要阅读THIS？

虽然你想陷阱只山口C粘贴，但这里是一个多场景，多列用户膏（其中一个是上校C）

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

    On Error GoTo Whoa 

    Application.EnableEvents = False 


    If Not Intersect(Target, Columns(3)) Is Nothing Then 
     If Not Target.Columns.Count > 1 Then 
      For Each aCell In Target 
       If aCell.Value <> "" And aCell.Offset(0, 1).NumberFormat = "$ 0.00" Then 
        aCell.Offset(0, 2).Value = aCell.Value * aCell.Offset(0, 1).Value 
       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 

Answer 2

在完整性和合作的精神，我在这里发布Siddharth Rout的方法的变体;不同之处在于，这不依赖于“单元格执行”，而是全部在一列中。这使它更清洁一点，并更容易适应其他情况。

Option Explicit 

Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim aCell As Range 
    Dim onlyThese As Range ' collection of ranges that, if changed, trigger some action 
    Dim cellsToUse As Range ' cells that are both in "Target" and in "onlyThese" 

    On Error GoTo Whoa 

    Application.EnableEvents = False 

    Set onlyThese = Range("C:C") ' in this instance, but could be anything - even a union of ranges 
    Set cellsToUse = Intersect(onlyThese, Target) 
    If cellsToUse Is Nothing Then GoTo Letscontinue 

    ' loop over cells that were changed, and of interest: 
    For Each aCell In cellsToUse 
     If aCell.Value <> "" And aCell.Offset(0, 1).NumberFormat = "$ 0.00" Then 
      aCell.Offset(0, 2).Value = aCell.Value * aCell.Offset(0, 1).Value 
     End If 
    Next 

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