Adding another variable to loop killing the speed.

psandvig

New Member
Joined
Apr 13, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I am creating and order form that adds items based on total need until we have a full truck. I have the form which pulls in the initial must purchase items as there are none on hand and calculates the total weight of the order. When the order does not meet the truck load the loop goes and adds 1 to the items with the lowest quantity on hand (identified with ranking on my raw data) then checks again to see truck space availability. Then loops until we reach weight needed. When I run this loop it is very fast and efficient.

I recently wanted to be able to include multiple store locations and just make the change to orderform and run the loop on my raw data. I added an IF and then statement to do this, but now the loop is very slow and sometimes does not always complete. Is there a faster way to do this? Below is the code:

VBA Code:
Sub Optomize_Order()

'Basic Parameters for entire code

'Set workbook and sheets

Dim Order_Op As Workbook

Set Order_Op = ThisWorkbook

Dim Order_Form As Worksheet

Set Order_Form = Order_Op.Sheets("Order_Form")

Dim Raw_Data As Worksheet

Set Raw_Data = Order_Op.Sheets("Raw_Data")

'Setting the goal

Set Weight = Order_Form.Range("A21")

'Setting the BU

Set BU = Order_Form.Range("G2")

'Setting Range to Adjust variable

Dim Amount As Range, cell_2 As Range

Set Amount = Raw_Data.Range("n1:n1500")

'Setting excess Ranking Range

Dim Excess As Range, cell As Range

Set Excess = Raw_Data.Range("q1:q1500")

'Loop until weight greater than 46200
Do While Weight <= 46200
If Weight.Value <= 46200 Then Raw_Data.Activate



StartRow = 1

LastRow = Cells.Find("*", Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row

For Row = StartRow To LastRow Step 1

If Cells(Row, 17) = 1 And Cells(Row, 2) = BU Then

Cells(Row, 14) = Cells(Row, 14).Value + 1

Order_Form.Activate

End If

Next Row

Loop
    
End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
843
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
Do While Weight <= 46200
If Weight.Value <= 46200 Then Raw_Data.Activate
This If statement doesn't make sense because you can only enter this loop if Weight is <= 46200. Also, if you want Raw_Data to be the active sheet, activate it outside the loop. However, it is inefficient to refer to sheet by depending on what is ActiveSheet. Do not Activate sheets to operate on them. You probably don't need to activate it.

You are activating Order_Form each pass through the For loop, when the condition is True. This is inefficient. You can do it outside the For loop, and probably don't even need to do it at all, per my remarks above.

I suggest you figure out when you need to refer to which sheet, and make your range references explicit. That is, instead of allowing this to depend on what is active:
VBA Code:
Cells(Row, 14) = Cells(Row, 14).Value + 1
be explicit:
VBA Code:
Order_Form.Cells(Row, 14) = Order_Form.Cells(Row, 14).Value + 1

Also if you disable screen updates it will speed things up. Put this line of code before your While loop:

VBA Code:
Application.ScreenUpdating = False

and after the loop

VBA Code:
Application.ScreenUpdating =True
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
323
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
looking over your code. I agree with 6StringJazzer above. However, I don't see how the Do While Loop is ever going to terminate. It depends on the variable Weight changing but no where in the Do Loop do you change it. If you are expecting the cell updates in the For/Next loop to somehow change the value of Weight then you will need to assign the value of the correct cell to Weight within the loop.

Also, you are depending greatly on the implicit default references when making Cell references. Highly recommend you use explicit references. for example here is the Do Loop as I modified based on my interpretation of your logic:
VBA Code:
Dim CurrentSheet As Worksheet
Set CurrentSheet = Raw_Data
Do While Weight <= 46200
  StartRow = 1
  LastRow = CurrentSheet.Cells.Find("*", CurrentSheet.Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row
  For Row = StartRow To LastRow Step 1
    If CurrentSheet.Cells(Row, 17) = 1 And CurrentSheet.Cells(Row, 2) = BU Then
      CurrentSheet.Cells(Row, 14) = CurrentSheet.Cells(Row, 14).Value + 1
      Set CurrentSheet = Order_Form
    End If
  Next Row
  Set CurrentSheet = Raw_Data
Loop

You could use the With/End With to reduce some of the repetitive references, but as said above don't depend on knowing which sheet is active, always be explicit.

Now, the way my loop is written, first time into the Do Loop happens when Weight is less than or equal to 46200. Every time you loop, you set StartRow to 1 and LastRow to the computed last row of which ever is the CurrentSheet, so first time in StartRow = 1 and LastRow = last row in Raw_Data. Then start the For Loop and check two cells for values. If they turn out to be false then loop to next row; if they are true increment a cell in CurrentSheet then change CurrentSheet to Order_Form then loop to next row. Now, if the If statement was true, and we have not reached LastRow then do If test on next row which is going to on the CurrentSheet which is Order_Form if we had gotten a true on the if statement on any previous record.

Again, I don't see how if you enter the Do Loop then you will never leave it because you never change the value of Weight.
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
843
Office Version
  1. 365
Platform
  1. Windows
I don't see how the Do While Loop is ever going to terminate. It depends on the variable Weight changing but no where in the Do Loop do you change it.

Again, I don't see how if you enter the Do Loop then you will never leave it because you never change the value of Weight.
Unfortunately there is not enough information to adequately assess this. Weight is a Range assigned to a cell, and we have no idea what is in that cell. If it is a formula, the result of the formula could be changed as a side effect of other updates going on in the loop. That is why it is sometimes difficult to analyze naked code without seeing the entire file.
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
323
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows

ADVERTISEMENT

Unfortunately there is not enough information to adequately assess this. Weight is a Range assigned to a cell, and we have no idea what is in that cell. If it is a formula, the result of the formula could be changed as a side effect of other updates going on in the loop. That is why it is sometimes difficult to analyze naked code without seeing the entire file.
I agree with you. The biggest part of programming is understanding the problem needing to be solved. Often times, the issue is that the problem needs to be re-defined. I have often found myself in the middle of a programming project and had to start over because I had not adequately defined the problem before starting the solution.
 

psandvig

New Member
Joined
Apr 13, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Yes, sorry I needed to be more explicit with detail. Weight is a formula as I add one to the values the formula adds the weight of the additional. I appreciate all the feedback very helpful as I continue to learn VBA.

Thanks!
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
323
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
Yes, sorry I needed to be more explicit with detail. Weight is a formula as I add one to the values the formula adds the weight of the additional. I appreciate all the feedback very helpful as I continue to learn VBA.

Thanks!
There are a lot of people here who seriously want to help others with their Excel and VBA concerns. The main impediment to their help is lack of information to work with. I for one would love to help you resolve this.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,730
Members
417,108
Latest member
Thein Than

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
Top