Adding another variable to loop killing the speed.

[psandvig]

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:

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

 

[6StringJazzer]

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:

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

be explicit:

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:

Application.ScreenUpdating = False

and after the loop

Application.ScreenUpdating =True

 

[vw412]

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:

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]

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]

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]

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]

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.