CODEWITHSUNDEEP
vba
Teemo
Teemo

Reputation: 1

Why is my loop so slow?

Is this because this is not an effective way to write this piece of codes or it is just that slow to work on excel like this? I am using excel 2007 in Windows 8 64bits. My office notebook is a very updated one with latest I5 cpu in it. Here is the code:

Private Sub CommandButton1_Click()

Sheets("data").Select


For rcounter = 2 To 45752

For xcounter = 26 To 50
For ycounter = 2 To 414

If (Cells(1, xcounter) = Cells(rcounter, 4)) Then
If (CInt(Cells(ycounter, 25)) = CInt(Cells(rcounter, 10))) Then

Cells(ycounter, xcounter).Value = Cells(ycounter, xcounter).Value + 1


End If
End If

Next ycounter
Next xcounter

Next rcounter


End Sub

Upvotes: 0

Views: 484

Answers (3)

RBarryYoung
RBarryYoung

Reputation: 56755

As explained in here, the most important step to enhancing the calculation performance of excel-vba is to use range-array copying instead of browsing cell-by-cell.

Here's how you can do this for your example:

Private Sub CommandButton1_Click()

Sheets("data").Select

Dim cels() as Variant
cels = Range("A1", "AA45752")

For rcounter = 2 To 45752

    For xcounter = 26 To 50
        For ycounter = 2 To 414

            If (cels(1, xcounter) = cels(rcounter, 4)) Then
                If (CInt(cels(ycounter, 25)) = CInt(cels(rcounter, 10))) Then

                    cels(ycounter, xcounter).Value = cels(ycounter, xcounter).Value + 1

                End If
            End If

        Next ycounter
    Next xcounter

Next rcounter

Range("A1", "AA45752") = cels

End Sub

This should be much faster. Though, of course, you are still looping (45752-1)*(50-25)*(414-1) or 472,379,075 times so it may still take a while. You may want to think about whether you really need to check all of those cells multiple times.

Upvotes: 2

sam092
sam092

Reputation: 1335

Don't know if this helps improve the situation. You can try moving the following line 

If (Cells(1, xcounter) = Cells(rcounter, 4)) Then

before 

For ycounter = 2 To 414

to reduce unnecessary comparisons

Upvotes: 1

Rick
Rick

Reputation: 1073

The problem is your nested loops, this can be broken down:

Your first loop runs 45,751 times.

Each time your first loop runs it will run your second loop 25 times resulting in your second loop running 1,143,775 times.

Each time your second loop runs it will run your third loop 413 times. Your third loop will run a grand total of 472,379,075 times.

I don't know what you are trying to achieve with this script but it is not very efficient.

Upvotes: 2

Related Questions