aaaaaa

Hi kokosek,

My bad not noticing the rules. There is a sample file.

My idea is that user inputs Parent code & value in sheet PLAN. Then macro is looping through BOM page to find all components of parent material and output it to OutputSheet. Then a simple calculation that im able to do by myself.
Just cant get through decomposing whole parent mat.

Data exported to this file is in sheet BOM & output in sheet Output.

Any ideas?

Thanks in advance!

That is the thing with multi-level BOM. Components might be done also by few other materials thats why it is called as parent in BOM. Thats the trick of extract i have. On the other hand in Output you have Parent-parent item that is made of few components, and those components might be done by another components. So there is fully finished item that is made of Comp 1,2,3,4 and Comp 4 is made of Comp 9 and Comp 10. Comp 10 can be made of Comp 11,Comp 12 etc. Thats why in output there is Item 1 in B column.

Does it make sense?

Thanks in advance!

Hey kokosek,

Ive been able to achieve multi-level explosion of FG, but no idea how to output also mid-lvl components. Anywhere i edit the code it triggers errors. Also if i try to make the calculation for output in the code it outputs mismatch error, so i left it as quantities for now. The code outputs lowest lvl components and its quanitites. Also how can i make Period inputs recursive? So i do not need to copy paste this code just to refer different cell of input.. Any ideas, help?

Sub BomExplosion()

Dim ModelCol As Range
Dim ModelQntyCol As Range
Dim ParentCol As Range
Dim Childcol As Range
Dim ChildQuantCol As Range
Dim ModelOutCol As Range
Dim PartCol As Range
Dim PartQntCol As Range
Dim BaseQty As Range

Set ModelCol = Sheets("PLAN").Columns("A")
Set ModelQntyCol = Sheets("PLAN").Columns("D")
Set ParentCol = Sheets("BOM").Columns("A")
Set Childcol = Sheets("BOM").Columns("C")
Set ChildQuantCol = Sheets("BOM").Columns("E")
Set ModelOutCol = Sheets("Output").Columns("A")
Set PartCol = Sheets("Output").Columns("D")
Set PartQntCol = Sheets("Output").Columns("F")


Const StartDataRow = 2
SummaryRowCount = StartDataRow

Lastrow = ParentCol.Cells(Rows.Count, 1).End(xlUp).Row


Set ParentRange = ParentCol
Set ChildRange = Childcol
Set TopLevelPart = ModelCol

For RowCount = StartDataRow To Lastrow
 Child = Childcol.Cells(RowCount, 1)
 
 Set c = ParentRange.Find(what:=Child, LookIn:=xlValues, lookat:=xlWhole)
 If c Is Nothing Then
  ChildQuant = ChildQuantCol.Cells(RowCount, 1)
  
  PartParent = ParentCol.Cells(RowCount, 1)
  
  
  Do
   Set c = ChildRange.Find(what:=PartParent, LookIn:=xlValues, lookat:=xlWhole)
   If Not c Is Nothing Then
   Quant = ChildQuantCol.Cells(c.Row, 1)
   ChildQuant = ChildQuant * Quant
   PartParent = ParentCol.Cells(c.Row, 1)
   End If
  Loop While Not c Is Nothing
  
  
  Set c = TopLevelPart.Find(what:=PartParent, LookIn:=xlValues, lookat:=xlWhole)
  If c Is Nothing Then
   MsgBox ("Cannot find top Level Part : " & PartParent)
  Else
   Quant = ModelQntyCol.Cells(c.Row, 1)
   ChildQuant = ChildQuant * Quant
   ModelOutCol.Cells(SummaryRowCount, 1) = PartParent
   PartCol.Cells(SummaryRowCount, 1) = Child
   PartQntCol.Cells(SummaryRowCount, 1) = ChildQuant
   SummaryRowCount = SummaryRowCount + 1
  End If
 End If

Next RowCount


End Sub

thanks in advance!

Hi,
I managed to solve problem with calculation by adding PerQty column to BOM Sheet and refering to it in code. For now it is just to output mid-lvl components, and eventually add an option for providing multiple periods.

Edited code & example within attached file.

I will be gratefull for any help

Thanks in advance!