CODEWITHSUNDEEP
vbaexcel
Walter
Walter

Reputation: 9

VBA nested for loop crashes

I have this function in VBA, everything works except when it reaches the For loop it just crashes and I have to close Excel and everything, not really sure how to fix it.

Private Sub checkDuplicates(x As Worksheet)
    Dim n As Integer, i As Long, j As Long

    Sort x
    addheader x, "checkDup."
    n = searchHeader(x, "checkDup.")
    x.Columns(n) = 1
    For i = 50 To 3 Step -1
        For j = 1 To 5
            If Not x.Cells(i, j) = x.Cells(i - 1, j).Value Then
                x.Cells(i, n) = 0
                Exit For
            End If
        Next j
    Next i
End Sub

Upvotes: 0

Views: 490

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

Based on the comment-conversation here, your code isn't "crashing".

It's working very hard and accessing cell values in a tight loop, which is the single slowest thing VBA code can do, and you're doing it for a large number of cells.

Wait it out. Excel "isn't responding" because it's busy running your loop and keeping up with all the updates going on.

Here's something that may help it complete faster:

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

' your code here

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

If that doesn't complete in a more reasonable amount of time, then you need to look into an array-based approach, where you dump the interesting range into an in-memory array, work against the array, modify the array values, and then dump the array onto the worksheet in a single write.

Upvotes: 4

Related Questions