下面的代码获取在交叉区域中输入的数字与A1和B1的值之和,然后在目标单元格中再次显示它,它显示正确的和,但excel错误(对象工作表的1004方法区域失败),并且excel重新启动,也许有人可以解释为什么它会这样做?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long, i
lRow = Cells(Rows.Count, 12).End(xlUp).Row
If Target.Cells.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("L20:L" & lRow)) Is Nothing Then
If Not IsNumeric(Target.Value) Then Exit Sub
Target.Value = Target.Value + Range("A1") + Range("B1")
End If
End Sub
已尝试以下操作,但仍有错误
If Not Intersect(Target, Range("L20:L" & lRow)) Is Nothing Then
If Not IsNumeric(Target.Value) Then Exit Sub
i = Target.Value
Target.Value = i + Range("A1") + Range("B1")
End If
1条答案
按热度按时间368yc8dk1#
What you have here is a classic example of an infinite recurring loop.
What is happening is that in your line:
you change the value of a cell. Even though this is done through VBA, this counts as a change to the worksheet. And since this module is within the
Worksheet_Change
module, it catches this change and starts this same worksheet change loop. This obviously will cause the change to happen again, and the loop to start again until it causes a stack overflow and crashes Excel.Edit: After some testing I found a workaround. It's by no means ideal but you can use a global variable to flag whether this loop has already run once this iteration: