cmondeau
Board Regular
- Joined
- Sep 23, 2014
- Messages
- 86
I am trying to create a Macro to optimize the lengths of one dimensional (1D) stock pipe using VBA and Solver. Hopefully any methods discussed in this thread will address not only my inquiries, but to the rest of the forum as well.
In an attempt to keep this thread organized, here is a link to my research, criteria, and other pertinent information towards the development of this code.
https://www.evernote.com/shard/s286...b492e95ce182/e5ec2c4e0bae3b5465321251966b5d86
Disclosure: I am a firm believer in the sharing of knowledge without constraint, much like Germany and it's recent educational reform
. Also, if it seems I am being nit-picky about complicated code, it's because I would like to learn, so any basic or simplified code, with lots of 'comments and explanations' next to your code is greatly appreciated! I am not asking for a hand-out, but would really like to understand the mechanics in the process of developing a productive tool. And if I'm out of line, slap me.
Here is a selection of data from a spool order for our pipe. This is what I've been using while developing a macro for the optimization macro. This contains a wide variety of the types of pipe needed for cutting (different sizes, lengths, materials). For myself, I will not be editing the data repot. I would simply like to pull my report from the part run, open Excel, press a lovely little "Solve" button, and have the Macro give me a report.
Again, this is only a snippet of a spool list, so I've been using dynamic ranges and such things (along with butterflies, sunshine, and AK-47's).
O.K. I think that's about it. As always any and all thoughts are welcomed, and I'll try my best to try anything and understand it. Thanks for your consideration!
In an attempt to keep this thread organized, here is a link to my research, criteria, and other pertinent information towards the development of this code.
https://www.evernote.com/shard/s286...b492e95ce182/e5ec2c4e0bae3b5465321251966b5d86
Disclosure: I am a firm believer in the sharing of knowledge without constraint, much like Germany and it's recent educational reform
. Also, if it seems I am being nit-picky about complicated code, it's because I would like to learn, so any basic or simplified code, with lots of 'comments and explanations' next to your code is greatly appreciated! I am not asking for a hand-out, but would really like to understand the mechanics in the process of developing a productive tool. And if I'm out of line, slap me.Here is a selection of data from a spool order for our pipe. This is what I've been using while developing a macro for the optimization macro. This contains a wide variety of the types of pipe needed for cutting (different sizes, lengths, materials). For myself, I will not be editing the data repot. I would simply like to pull my report from the part run, open Excel, press a lovely little "Solve" button, and have the Macro give me a report.
Code:
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Item No[/TD]
[TD="class: xl63, width: 64"]Pipe Size[/TD]
[TD="class: xl63, width: 64"]Length[/TD]
[TD="class: xl63, width: 64"]Description[/TD]
[TD="class: xl63, width: 64"]Type[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1284[/TD]
[TD="class: xl63"]0 1/2"[/TD]
[TD="class: xl63"]2"[/TD]
[TD="class: xl63"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1252[/TD]
[TD="class: xl63"]0 1/2"[/TD]
[TD="class: xl63"]2"[/TD]
[TD="class: xl63"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1162[/TD]
[TD="class: xl63"]0 1/2"[/TD]
[TD="class: xl63"]2"[/TD]
[TD="class: xl63"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1429[/TD]
[TD="class: xl63"]0 3/4"[/TD]
[TD="class: xl63"]3"[/TD]
[TD="class: xl63"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1276[/TD]
[TD="class: xl63"]0 3/4"[/TD]
[TD="class: xl63"]2"[/TD]
[TD="class: xl63"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1269[/TD]
[TD="class: xl63"]0 3/4"[/TD]
[TD="class: xl63"]3"[/TD]
[TD="class: xl63"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1251[/TD]
[TD="class: xl63"]1"[/TD]
[TD="class: xl63"]7"[/TD]
[TD="class: xl63"]A53 CW Sch 40 Blk T&C[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1157[/TD]
[TD="class: xl63"]1"[/TD]
[TD="class: xl63"]12"[/TD]
[TD="class: xl63"]A53 CW Sch 40 Blk T&C[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1247[/TD]
[TD="class: xl63"]1"[/TD]
[TD="class: xl63"]12"[/TD]
[TD="class: xl63"]A53 CW Sch 40 Blk T&C[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
</tbody>[/TABLE]Again, this is only a snippet of a spool list, so I've been using dynamic ranges and such things (along with butterflies, sunshine, and AK-47's).
O.K. I think that's about it. As always any and all thoughts are welcomed, and I'll try my best to try anything and understand it. Thanks for your consideration!
