Forum Discussion
Create a product tree using vba
Hi,
I need to create a product tree and I would like to do this using vba, is that possible? I already have all the components so I just need to create relations between them. Please look at the exemple below.
To manufacture the "Bateria PICINW interna com conector" I need 4 components. I want to create the second table with the collum "Production capacity" which is the stock of "Bateria PICINW interna com conector" plus the minimum stock between the 4 components.
| Code | Description | Stock |
| 4 | BATERIA PICNIW INTERNA COM CONECTOR | 56 |
| 4.1 | ALOJAMENTO MOLEX 2P CLIKMATE PLUG | 478 |
| 4.2 | TERMINAL MOLEX CLIKMATE AWG 26-28 | 991 |
| 4.3 | BATERIA INTERNA 3,7V 240 MAH | 300 |
| 5 | LAMINA INSTRUMENTADA PICNIW | 175 |
| Code | Description | Production capacity |
| 4 | BATERIA PICNIW INTERNA COM CONECTOR | 56 + minimum(478,991,300) |
| 5 | LAMINA INSTRUMENTADA PICNIW | 175 |
My first ideia was to create some loops and insert the values in a array so I could chose the minimum but I'm having some dificuties, could someone help me please?
HSalles In the attached I formatted the orig table as a table called "Products" and used this:
=MIN(FILTER(Products[Saldo/qtd],LEFT(Products[NIVEL],LEN(F2))=LEFT(F2,99),0))basically filters the list based on having the same prefix
BTW, I realize this isn't VBA solution but anything using a cell formula can be replicated in VBA so the answer is yes. I just prefer to avoid VBA when possible due to compatibility and security concerns.
- mtarler
Unfortunately this isn't the solution. If you take a look at your worksheet, at the column max and row 2, the value should be 356 (56 that I have stocked plus 300 that I can manufacture). The same goes to the row 3, the value should be 175 (175 that I have stocked plus 0 athat I can manufacture).
Sorry if I didn't made my self clear, I think that it's better now.
