For Loop is too slow

A

alexaronson

Active Member
Joined
Sep 30, 2005
Messages
314
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?
Code: 
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
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
Click to expand...
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.:)
 
Upvote 0
alexaronson said:
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.
Click to expand...

Pleased to read it helped. Thanks for the feed back.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Compare Demand Data to Stock Data
Replies
6
Views
555
Bosquedeguate
Bosquedeguate
T
  • Question Question
The macro takes too long to run
2
Replies
10
Views
879
johnnyL
J
M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
249
igold
igold
H
  • Solved
VBA code to alter specific range to selection
2
Replies
10
Views
602
Joe4
Joe4
S
  • Question Question
Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called
Replies
0
Views
946
Steve48
S

Forum statistics

Threads
1,224,514
Messages
6,179,219
Members
452,895
Latest member
BILLING GUY

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top