VBA Loop To Add 'left over' Units

[npb006]

Hi,

I've been having a problem trying to come up with a VBA loop. Here's what I'm trying to accomplish. I have a spreadsheet looking at potential orders based on availability/selling. However, certain times require a set amount of units to reach a discount, minimum quantities set by the vendor, etc.

My spreadsheet will let me know the "recommended" order. However, due to selling or other factors, this number will be short from the "needed" or "desired" quantity to order. I want to add one unit to the lowest number in the "Weeks of Supply" column. This way, if I have to add an additional unit, it's going to something that will sell the quickest and needs it, comparatively, the most.

For example:

I want to order 16 units. The recommendations are as such.

S - 4
M - 2
L - 1
XL - 6

13 units were recommended but I need 16 to place the order.

Here's my VBA Loop that I need:

Look at Cell T5 (where my remaining units to add are)

If T5 =0 Then Do nothing
If T5<>0 Then Find smallest value in range U7:U107 (I have some interesting size ranges but 100 should cover it)
Offset by (0,-2) (In column S. This is where I have the recommended units per size broken out)
Add 1 to that Cell in Column S
Subtract 1 from T5
Loop

This will then loop and stop because T5 will eventually = 0 but the WOS will change as you add 1 unit at a time.

Let me know if you have any questions! I REALLY APPRECIATE ANY HELP. See attached for the screenshot. (*Please ignore the Rounding Column. It's for a different function in the sheet)

Capture.PNG

[stnkynts]

If T5<>0 Then Find smallest value in range U7:U107

If you are finding the smallest value each time you will just be doing the same action to the range X number of times. Am i missing something? Also, pictures don't help much. A copy of your workbook would be much more helpful.

[npb006]

I cannot share the workbook because it has information that I cannot divulge.

However, when you add 1 to the recommended units on the first loop, this will then change the Weeks Of Supply for that size. It will then potentially change the smallest weeks of supply since we are adding an additional on hand unit to that size.

Weeks of Supply = On Hand Units/Units Sold in a Time Frame.

So when the loop runs for the second time (in my example after you ran it once, the number of units left to add would go from 3 to 2), it will have to search the range again because the lowest weeks of supply could now be a different size.

Does that make sense?

[npb006]

This is what I have tried to do so far... if this helps


Sub AddUnits()

Dim leftover As Integer
Dim rng As Range
Dim minimum As Double
Dim result As String

leftover = Range("T5").Value

If leftover = 0 Then
result = "done"
Else

Set rng = Sheet1.Range("U7:U1000")

minimum = Application.WorksheetFunction.Min(rng).Select

ActiveCell.Offset(0, -2).Select

ActiveCell.Value = ActiveCell.Value + 1

Range("T5").Value = Range("T5").Value - 1

End If
Loop
End Sub

[stnkynts]

Please Login or Register  to view this content.

[npb006]

Perfect! Thank you.

[npb006]

Would there be any reason why it would stop adding numbers to column S and only subtract from T5?

[stnkynts]

Yes, many, but without a workbook I am not going to even try to troubleshoot.

[npb006]

Okay, let me edit the document so the numbers are fake and then I'll share it.