Help on a Knapsack variation- using VBA

doctorhifi

New Member
Joined
Aug 13, 2013
Messages
19
Let’s say I have endless sheets of wood that each measure 48” wide x 96” long.
And out of those sheets I am trying to cut strips that are various widths but always the same length so I am always cutting the sheets along the long dimension.
The quantity of strips as well as the width of each strip will vary but there would never be more than 100 strips needed.
Example:
I need (4) strips that are 24.5” wide
I need (3) strips that are 17.33” wide
I need (3) strips that are 36” wide
I need (2) strips that are 6” wide
Etc, etc.

-I am trying to create a VBA formula that would look at all of the quantities and widths of strips and determine how many 48x96 sheets I would need in total.
-It would need to look at all the strips and group them together in order to reduce waste.
-It would need to report which strips were coming from each sheet, something like this:
Sheet #1= (1 of 4) @ 24.5” + (1 of 3) @ 17.33” + (1 of 2) @ 6”
Sheet #2= (2 of 4) @ 24.5” + (2 of 3) @ 17.33” + (2 of 2) @ 6”
Sheet #3= (3 of 4) @ 24.5” + (3 of 3) @ 17.33”
Sheet #4= (4 of 4) @ 24.5”
Sheet #5= (1 of 3) @ 36” W
Sheet #6= (2 of 3) @ 36” W
Sheet #7= (3 of 3) @ 36” W
Total sheets needed= 7

I've been unable to figure out how to do this using the Solver plug-in and would actually prefer to not use Solver if possible.
My VBA skills are embarrassing so I don't even have the start of a subroutine to share.
Thanks
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Thanks for those links. I'm actually looking to create the data using Excel VBA and not an external program because I need to use the results for other calculations with an Excel workbook.
 
Upvote 0
I just looked at the MaxCut link provided and the Community Edition for personal use allows you to copy/paste the results to and from Excel.

The free version allows the below


Excel 2010
ABC
12Grouping and Ungrouping of items
13Optimize Waste or Time to Cut
14Copy / Paste to and from Excel
15File Export
16Calculate Edge Banding
17Create Customer Proposals
18Pricing Tool
19Shared Materials (multi-users)
20Raw Materials Import
Sheet1


It doesn't have the following...


Excel 2010
A
1Feature Name
2Presaved Library Items
3Parts allowed per project
4Quantities allowed per part
5Material Cost vs Selling Price Comparisons
6Custom Blade Thickness per Sheet Material Type
7Accurate Holes & Grooving Detail for Panels
8Mass change selected items by Material, Part Tag, Dimension, etc
9Customize reports to include your Company Logo
10MaxCut Advertising removed from all Reports/Labels
11No time delayed start up screen when opening the program
Sheet1
 
Last edited:
Upvote 0
I've been trying MaxCut for the last few days and determined it won't meet my needs so I am still looking for a VBA solution.
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top