I have this optimization problem that I would like to solve, but I have no idea how to go about it as my knowledge in VBA is very very basic. So I have multiple selection checkboxes which when checked populate a cell, like so:
p1.png
What I would like to achieve is similar to a cash withdrawal system from a bank, where when you select the amount you want, it automatically populates the number and the value of cash you'd like. For example, if you wanted to withdraw $100, it could say give you 2 x $20, 1 x $10 and 1 x $50, and when you deselected and amount, other amounts would increase to match $100.
In my case, when the fields "Drilled Hole Volume" and "Volume of Bolt" are populated, I'd like for the macro to look into the following data values:
p2.png
Then I would like it to choose a maximum of 2 cartridges that will populate the cell for the field "Volume of Cartridges" so that the field "Remaining Volume" becomes very close to zero. I would also like the macro to populate the "Quantity" field in image 1 and update the "Cartridge" ActiveX ListBox with the proper selection.
When someone clicks the down arrow, the macro would recalculate what the next best value that would result in 0 being and fill the "Quantity" field and update the "Cartridge" ActiveX ListBox again. In the event that someone can enter 2 of the same kind, but the "Remaining Volume" value does not go to zero, then it won't let them do it. However if the value becomes close to being 0, it will let them choose 2 of the same kind. So the process would look something like this.
1) I select my options using the checkboxes I have:
p9.png
2) Then, the macro would automatically populate its best options that would result in the values being close to 0 and fill the "Quantity" field, update the ListBox and populate the cell "Cartridge". For example, the quantities for 28mm Dia. x 24" and 25mm Dia. x 12" are 1 each, and their added volumes is 31.44, and with my other selections, the "Remaining Volume" cell becomes 0.37 which is negligible and round able to 0:
p10.png
3) When I hit up for the same parts, but the values become negative, then I would get a popup that says "Invalid selection": (Can't show this yet).
4) When I hit down, the macro will recalculate the next best option that will result in the value "Remaining Volume" getting closest to 0, and then show it again like so:
p11.png
The value for the field "Remaining Volume" is 1.450. This is also negligible. The range of negligible values is 0 - 1.8.
Any help or guidance would be greatly appreciated.
Bookmarks