Why does Excel crash on Worksheet_Change event that changes the worksheet?

Question

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lrow1 As Long
Dim lrow2 As Long
Dim cell As Range
Dim sell As Range

lrow1 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lrow2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

For Each cell In Sheets("Sheet2").Range("A1:A" & lrow2)
    For Each sell In Sheets("Sheet1").Range("A1:A" & lrow1)
        If cell.Value = sell.Value Then
            cell.Offset(0, 1).Value = sell.Offset(0, 1).Value
        End If
    Next sell
Next cell

End Sub

The first entry works, brings the info correctly. With the second entry Excel crashes.

I assume one of those sheets are the active sheet, you would end up with an infinite loop. You need to set the target range as well as dealing with enable events. — Davesexcel, Jan 18 '20 at 03:42
Suppose the spreadsheet has 1 million rows, so each iteration `For Each cell In Sheets ("Sheet2")` the procedure will run 1 million times `For Each sell In Sheets ("Sheet1")` even if you use enableevent, I'm not sure it will run quickly. — Dang D. Khanh, Jan 18 '20 at 05:25
[BIG HINT](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure) — Siddharth Rout, Jan 18 '20 at 06:02

Ricardo Diaz · Answer 1 · 2020-01-18T18:54:39.183

I tried to refactor your code and clean it up in some parts.

Read code's comments and adjust it to fit your needs.

EDIT: I assume that your code is placed in sheet1 otherwise you'd need to check if the Target range of the event doesn't intersect with the range that is changed in this line: evalCellSheet2.Offset(0, 1).Value = evalCellSheet1.Offset(0, 1).Value

Private Sub Worksheet_Change(ByVal Target As Range)

    ' Give meaningful names to your variables
    Dim evalSheet1 As Worksheet
    Dim evalSheet2 As Worksheet

    ' Try not to use variable names that may conflict with Excel/VBA objects, properties, etc,
    Dim evalCellSheet1 As Range
    Dim evalCellSheet2 As Range

    Dim lastRowSheet1 As Long
    Dim lastRowSheet2 As Long

    ' Fully qualify objects
    Set evalSheet1 = ThisWorkbook.Sheets("Sheet1")
    Set evalSheet2 = ThisWorkbook.Sheets("Sheet2")

    ' Reuse objects you have already set
    lastRowSheet1 = evalSheet1.Cells(evalSheet1.Rows.Count, 1).End(xlUp).Row
    lastRowSheet2 = evalSheet2.Cells(evalSheet2.Rows.Count, 1).End(xlUp).Row

    For Each evalCellSheet2 In evalSheet2.Range("A1:A" & lastRowSheet2)
        For Each evalCellSheet1 In Sheets("Sheet1").Range("A1:A" & lastRowSheet1)
            If evalCellSheet2.Value = evalCellSheet1.Value Then
                ' As you're changing values, disable events and then reenable it
                Application.EnableEvents = False
                evalCellSheet2.Offset(0, 1).Value = evalCellSheet1.Offset(0, 1).Value
                Application.EnableEvents = True
            End If
        Next evalCellSheet1
    Next evalCellSheet2

End Sub

Let me know if it works

Why does Excel crash on Worksheet_Change event that changes the worksheet?

1 Answers1