PDA

View Full Version : Stock Length Calculator



didless
12-24-2016, 03:14 AM
Hi, I am a bit of a novice at VBA, although I have been experimenting with it for about a year or so and learning a lot by trial (and lots of) error! Basically I would like to create a macro to analyse a set of values and return a combination of those values that most closely sums up to a predetermined value.
The application is for construction, where we purchase timbers in stock lengths, usually 3.66m or 5.49m. This stock length then gets cut into certain lengths (for window sills). What I want to produce is a calculator to determine how many stock lengths are required in a way that minimises wastage.
Let's say that the values are as follows:


Value ref
Required length (m)
Stock length=3.66


a
2.1



b
1.2



c
0.5



d
1.8



e
2.5



f
0.75



With my current knowledge of VBA, the only way I can see how I could accomplish this would be to have multiple if functions to analyse the data, I.e:
if value a < 3.66 then
if value a + value b < 3.66 then
if value a + value b + value c < 3.66 then... (continue to work its way through the list).
When adding a value exceeds the required stock length total, the macro cuts the values required from the list and inserts into a cell as the sum of the values (e.g. =value a+value b+value c) and then returns to the list to analyse the rest of the data, continuing until the list is empty. If the values were sorted by size, largest first, this should work to give a result minimises wastage.
The main problem I can see with doing it this way is the number of if functions required for every possible permutation of value combinations (a+b+c, a+b+d, a+b+e etc ad nauseum!). Is there any other way of analysing the data or any function that perhaps I'm not aware of that anyone can suggest that will work more elegantly and efficiently? I'm not asking for a full VBA code, as I like to learn by writing it myself, but just wondered if anyone can suggest a way around it?
Many thanks!
:)