Forum Discussion

HSalles's avatar
HSalles
Copper Contributor
Jan 18, 2023

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. 

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?

excel
Formulas and Functions
Macros and VBA
office 365
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jan 18, 2023

    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.

    • HSalles's avatar
      HSalles
      Copper Contributor
      Jan 18, 2023
      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.
      • mtarler's avatar
        mtarler
        Silver Contributor
        Jan 18, 2023

        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))

Resources