Mr. Excel,
I am attempting to find/write some VBA code for looking at a manufacturing Bill fo Material for a finished product, and deleting any Bill of Material Lines below purchased parts. Assuming the "Line#" heading below is in cell A1, can you help me with some VBA code for this? The "action" shows which lines should be deleted through the code. "lvl" stands for the level on an indented bill of material. Level 0 is the finished product, and levels 1 through 4 are different assemblies of the product and purchased parts which make up the finished product. A level 4 would be a part or subassembly which is manufactured into a level 3, and a level 3 into a level 2, etc. "MP" is whether a part is manufactured or purchased. As you can see, the first line which would be deleted is line 10, because it is a single line bill of material under a purchased part.
Everything below the level 1 purchased part (line 20) until the next level 1 part (line 30) would be deleted because this circuit board (part number 1803260.6</SPAN>) is no longer being manfuactured in-house, but is being purchased as an assembly from a third party. I am using Excel 2010 on Windows 7. I am thinking I will need a some looping code which looks at the 2 parameters, the "MP" and the "lvl". in the case of the line 20 purchased part, it would delete any lines below it which have an "lvl" which is > its lvl (1), until it reaches an lvl which = the lvl of this purchased part (in this case 1). It will then move on to the next line and re-evaluate until it reaches the end of the data set.
thanks so much for your help!
<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>
I am attempting to find/write some VBA code for looking at a manufacturing Bill fo Material for a finished product, and deleting any Bill of Material Lines below purchased parts. Assuming the "Line#" heading below is in cell A1, can you help me with some VBA code for this? The "action" shows which lines should be deleted through the code. "lvl" stands for the level on an indented bill of material. Level 0 is the finished product, and levels 1 through 4 are different assemblies of the product and purchased parts which make up the finished product. A level 4 would be a part or subassembly which is manufactured into a level 3, and a level 3 into a level 2, etc. "MP" is whether a part is manufactured or purchased. As you can see, the first line which would be deleted is line 10, because it is a single line bill of material under a purchased part.
Everything below the level 1 purchased part (line 20) until the next level 1 part (line 30) would be deleted because this circuit board (part number 1803260.6</SPAN>) is no longer being manfuactured in-house, but is being purchased as an assembly from a third party. I am using Excel 2010 on Windows 7. I am thinking I will need a some looping code which looks at the 2 parameters, the "MP" and the "lvl". in the case of the line 20 purchased part, it would delete any lines below it which have an "lvl" which is > its lvl (1), until it reaches an lvl which = the lvl of this purchased part (in this case 1). It will then move on to the next line and re-evaluate until it reaches the end of the data set.
thanks so much for your help!
| line#</SPAN> | lvl</SPAN> | MP</SPAN> | part number</SPAN> | description</SPAN> | action</SPAN> |
| 1</SPAN> | 0</SPAN> | M</SPAN> | 1954321</SPAN> | Calculator</SPAN> | |
| 2</SPAN> | 1</SPAN> | P</SPAN> | 1123544</SPAN> | Battery</SPAN> | |
| 3</SPAN> | 1</SPAN> | P</SPAN> | 1654123</SPAN> | Screws</SPAN> | |
| 4</SPAN> | 1</SPAN> | P</SPAN> | 1156211</SPAN> | Front cover plate</SPAN> | |
| 5</SPAN> | 1</SPAN> | P</SPAN> | 1156112</SPAN> | Back cover plate</SPAN> | |
| 6</SPAN> | 1</SPAN> | P</SPAN> | 1156412</SPAN> | packaging</SPAN> | |
| 7</SPAN> | 1</SPAN> | P</SPAN> | 1654232</SPAN> | instruction manual</SPAN> | |
| 8</SPAN> | 1</SPAN> | M</SPAN> | 1149837</SPAN> | battery housing</SPAN> | |
| 9</SPAN> | 2</SPAN> | P</SPAN> | 1287622.3</SPAN> | ||
| 10</SPAN> | 3</SPAN> | P</SPAN> | 2425407.6</SPAN> | delete</SPAN> | |
| 13</SPAN> | 2</SPAN> | M</SPAN> | 1838763.5</SPAN> | ||
| 14</SPAN> | 3</SPAN> | P</SPAN> | 1976548.8</SPAN> | ||
| 15</SPAN> | 4</SPAN> | P</SPAN> | 1114334.1</SPAN> | delete</SPAN> | |
| 16</SPAN> | 4</SPAN> | P</SPAN> | 1252119.4</SPAN> | delete</SPAN> | |
| 17</SPAN> | 2</SPAN> | P</SPAN> | 1389904.7</SPAN> | ||
| 20</SPAN> | 1</SPAN> | P</SPAN> | 1803260.6</SPAN> | Circuit board</SPAN> | |
| 21</SPAN> | 2</SPAN> | m</SPAN> | 1941045.9</SPAN> | board</SPAN> | delete</SPAN> |
| 22</SPAN> | 3</SPAN> | p</SPAN> | 2078831.2</SPAN> | copper conductors</SPAN> | delete</SPAN> |
| 23</SPAN> | 3</SPAN> | m</SPAN> | 2216616.5</SPAN> | switches</SPAN> | delete</SPAN> |
| 24</SPAN> | 4</SPAN> | p</SPAN> | 2354401.8</SPAN> | switch cover</SPAN> | delete</SPAN> |
| 25</SPAN> | 4</SPAN> | P</SPAN> | 2492187.1</SPAN> | inner switch</SPAN> | delete |
| 26</SPAN> | 2</SPAN> | m</SPAN> | 2629972.4</SPAN> | delete</SPAN> | |
| 27</SPAN> | 3</SPAN> | p</SPAN> | 2767757.7</SPAN> | delete</SPAN> | |
| 28</SPAN> | 2</SPAN> | p</SPAN> | 2905543</SPAN> | delete</SPAN> | |
| 29</SPAN> | 2</SPAN> | p</SPAN> | 3043328.3</SPAN> | delete</SPAN> | |
| 30</SPAN> | 1</SPAN> | m</SPAN> | 3181113.6</SPAN> | ||
| 31</SPAN> | 2</SPAN> | p</SPAN> | 3318898.9</SPAN> |
<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>
