Create a product tree using vba

Copper Contributor

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. 

CodeDescriptionStock
4BATERIA PICNIW INTERNA COM CONECTOR56
4.1ALOJAMENTO MOLEX 2P CLIKMATE PLUG478
4.2TERMINAL MOLEX CLIKMATE AWG 26-28991
4.3BATERIA INTERNA 3,7V 240 MAH300
5LAMINA INSTRUMENTADA PICNIW175

 

CodeDescriptionProduction capacity
4BATERIA PICNIW INTERNA COM CONECTOR56 + minimum(478,991,300)
5LAMINA INSTRUMENTADA PICNIW175

 

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?

3 Replies

@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.

@HSalles updated

=XLOOKUP(F2,Products[NIVEL],Products[Saldo/qtd],0)+MIN(FILTER(Products[Saldo/qtd],(LEFT(Products[NIVEL],LEN(F2))=LEFT(F2,99))*(Products[NIVEL]<>F2),0))