For Loop is too slow

[alexaronson]

Hello and thank you for your time.

I need some help to speeding this For loop up.

Currently, if lastRow = 1,000, the macro is staking 97 secondes to complete. However, my lastRow has a value of around 43,000 cells meaning this macro would take about 69 minutes to complete. Right now, the inner For loop is 4 iterations long but will be expanding to 15 iterations in the future.

If I go an manually create the logic in excel and copy and paste it, it only takes me 2 minutes to get the answer which tells me something is just not right with this code.

Any Suggestions on how to speed it up?

For i = i To lastRow
    a = 1
    Demand = wsDMDD.Cells(i, 5) + wsDMDD.Cells(i, 6)
    FreeStock = wsDMDD.Cells(i, lastColumn + 2)
    For a = a To lastColumn - 5
        Select Case FreeStock
        Case 0
            wsDMDD.Cells(i, lastColumn + 2 + a) = Demand
        Case Is > 0
            If FreeStock > Demand Then
                wsDMDD.Cells(i, lastColumn + 3) = 0
                FreeStock = FreeStock - Demand
                Demand = 0
            End If
            If FreeStock < Demand Then
                Demand = Demand - FreeStock
                FreeStock = 0
                wsDMDD.Cells(i, lastColumn + 2 + a) = Demand
            End If
        End Select
        
        Demand = wsDMDD.Cells(i, 6 + a)
    Next
    FreeStock = 0
Next

 

[Trevor G]

Have you switched the updates of etc.

Sub autoUpdate()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

'Do your code then switch back on

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True


End Sub

 

[ilya2004]

I've found this article to be useful before. It has a whole section on how to get what you need while limiting loops.

http://www.ozgrid.com/VBA/VBALoops....DM3OmNvcnByYWRpdXNzc286PgyujZCJloRKDuqPxAWiig==

Another option which I have used is putting your if statements in order of either likelyhood of being true and/or quickness of execution, that way, the program doesn't have to process unneccessary lines as often as possible.

 

[alexaronson]

Trevor,

I had the ScreenAppilications code but not the other two. When I did that the program went warp speed.

Ilya,

Very interesting article. I am giong to have to try the examples out to get a real feel of the lesson.

Thank you both for your help.

 

[Norie]

If you can manually create this in Excel why not use code to do that?

That's kind of the idea of macros really, automation of repetitive manual tasks.^*

It's pretty straightforward to enter formulas with code and copy and paste them with code.

^* I'm sure someone can give a better, correcter(?), explanation than that generalisation.

 

[Trevor G]

Trevor,

I had the ScreenAppilications code but not the other two. When I did that the program went warp speed.

Ilya,

Very interesting article. I am giong to have to try the examples out to get a real feel of the lesson.

Thank you both for your help.

Pleased to read it helped. Thanks for the feed back.

 

[ilya2004]

My pleasure