Solved

# Formula or Macro help?

Posted on 2011-10-27
Medium Priority
160 Views
I am working on a load plan sheet for new associates in our business. We want to maximize the trucks that are being shipped. We have two different sets:

FCA and CIF

Each one has a min and max weight, our goal is to have as many close to the max, but also looking at the last one keeping it as high as possible. This means that the other trucks may be closer to the mins then the max's to make the last truck as heavy as possible.

I have attached a sheet with comments and examples of what i am trying to do. I am not sure if its best to do formulas, or a macro to get this done. The min and maxs can change from time to time, and the weight of the load that comes in changes sometimes 100,000lbs sometimes 500,000lbs and it has to be broken up. This sheet is going to be used to help the new associate understand the best way to break it up so we maximize our truck weights and have a higher average.

What we want to make sure of is the last truck which is always smaller, is as big as we can make it, with out taking the other trucks below the mins. This keeps the average higher over a period of time.

If anyone has suggestions please help, I know what i want it to do, just can't figure out how to do make it use the logic.

0
Question by:dn920
• 6
• 4

LVL 24

Expert Comment

ID: 37038368
In your FCA example you chose 40/30/30/30 but why not 39/31/30/30 or 35/30/30/35? Should the first one always be the (equal) biggest?
0

Author Comment

ID: 37038399
Stephen, I did it just to make it simple for myself, it could be any combo as long as it keeps it above the mins. A lot of times you will see a last truck that can't get to 30k, it will be 14k, but newer associates make the other trucks 45,000, when they could cut off some from these trucks to raise the 14k higher in example.

Basically what ever the lump sum total of the order it needs to break down to keep as many as possible over over the mins, and what ever is left as close to the min as possible with out taking the others under.

Hope this makes sense?
0

LVL 24

Expert Comment

ID: 37038402
Yes, I think so, but needs a little thought. Others may jump in in the meantime.
0

LVL 24

Expert Comment

ID: 37043915
I just had a look at this again and a couple of problems arose. One, I can't see a difference in your example between FCA and CIF. The target appears to be the same? Two, I'm struggling because of the lack of constraints - there seem ot be an infinite (slight exaggeration maybe) number of ways of solving each problem. May just need more examples, perhaps it will become clearer.
0

Author Comment

ID: 37044440
The only difference between the two are the max and min weights. CIF has a min 45000 and max of 46500, and FCA has a min of 30000 and a max of 45000.

You're having the same problem I am having. The basic concept that I have been messing with is dividing the total weight B2 (what ever the weight may be) then to look at the last truck in this case 4, if this is less than min taking some weight off of the other trucks (some how telling it to not drop those below the min weights) and adding that weight it took from the others to the last truck.

I have seen this done in Excel before, but I can not figure out how they did it. I started to write some VBA Macro code but I couldn't get that to work it would break it down and leave the last truck at what ever was left.

0

LVL 24

Accepted Solution

StephenJR earned 2000 total points
ID: 37044557
Had a play and came up with something, but not sure if it's right.
``````Sub x()

Dim nMin As Long, nMax As Long, nDiff As Long, nRow As Long

Sheets("FCA Example").Activate

nMin = WorksheetFunction.RoundUp(Range("B2") / Range("L4"), 0)
nMax = WorksheetFunction.RoundUp(Range("B2") / Range("J4"), 0)

Range("E3").Resize(nMin).Value = Range("J4")
nRow = nMin

Do While nRow <= nMax
nDiff = Range("B2").Value - Range("G9").Value
If nDiff > Range("L4") Then
Range("E3").Offset(nRow) = Range("J4")
nDiff = nDiff - nMin
nRow = nRow + 1
Else
Range("E3").Offset(nRow) = nDiff
Exit Sub
End If
Loop

End Sub
``````
0

Author Comment

ID: 37045695
Stephen, this looks like it works and serves the purpose with out a doubt, I was trying to do this backwards by looking at your coding, it takes out some of the logic but still makes them have to validate the load plan.

So using this, if I want to change it to the CIF min/max I just have to adjust the ranges and it would then look at the CIF max/min correct?

Just want to make sure I am looking at this right. Thank you so much for your help!
0

LVL 24

Expert Comment

ID: 37045726
For CIF, you would only need to change line 5 to activate the other sheet. Tbh the code could be tidied up, or could be turned into a function so you could pass arguments such as FCA/CIF (even min/max/total).
0

Author Closing Comment

ID: 37045969
Thank you so much, I have been racking my brain on this one for a few weeks trying things. Your help is greatly appreciated and very easy to follow!
0

LVL 24

Expert Comment

ID: 37046012
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diā¦