工作表更改事件粘贴值后不起作用 [英] Worksheet Change event not working after pasting values

问题描述

我有一个代码在 Worksheet_Change

如果列9更新，那么列8将自动通过与col 9和col 11相乘来填充。

If a column 9 is updated then the column 8 will be automatically populated by multiplying with col 9 and col 11.

但是当用户粘贴列中的值时，更改事件无法正常工作。只有Col 8中的第一个单元格被更新。

But when the user pastes the values in the column, the change event does not work correctly. Only the first cell in the Col 8 gets updated.

如何克服这个？

推荐答案

/ p>

Try this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa
    Application.EnableEvents = False

    If Target.Columns.Count > 1 Then GoTo LetsContinue

    If Not Intersect(Target, Columns(9)) Is Nothing Then
        Dim aCell As Range

        For Each aCell In Target
            aCell.Offset(, -1).Value = aCell.Value * aCell.Offset(, 2)
        Next
    End If

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

这篇关于工作表更改事件粘贴值后不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！