error-excel restart worksheet change macro-CodePudding

the code below gets the sum of the entered number in the intersect range and the value of A1 and B1 then display it again in the target cell, it shows the correct sum but excel errors (1004 method range of object worksheet failed) and excel restarts, maybe someone can explain why it does that?

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

tried below but still errors

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

CodePudding user response：

What you have here is a classic example of an infinite recurring loop.

What is happening is that in your line:

Target.Value = Target.Value   Range("A1")   Range("B1")

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:

Dim run As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long, i

If run = True Then
    run = False
    Exit Sub
End If

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
            run = True
            Target.Value = Target.Value   Range("A1")   Range("B1")
    End If

End Sub