I want to be able to calculate the total quantity of parts in a multi BOM.
The BOM consists of 3 columns. The 1st column is the Level on the BOM, the 2nd column is part and the 3rd column is the quantity on the BOM. So for every car there are 4 wheels, for every wheel there are 1 rim and 4 nuts.
The attached spreadsheet contains the BOM data and code.
I have written some VBA to calculate the total quantity but the code is limited. Some BOMs I work with have many levels. I dont want to have to replicate my code for every level. My code is as follows. Please can someone simplify this code.
Sub totalqty()
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A2:A" & LastRow)
If cell.Value = 1 Then
Dim Level1 As Single
Level1 = cell.Offset(0, 2).Value
cell.Offset(0, 3).Value = Level1
ElseIf cell.Value = 2 Then
Dim Level2 As Single
cell.Offset(0, 3).Value = Level1 * cell.Offset(0, 2).Value
Level2 = cell.Offset(0, 3).Value
ElseIf cell.Value = 3 Then
Dim Level3 As Single
cell.Offset(0, 3).Value = Level2 * cell.Offset(0, 2).Value
Level3 = cell.Offset(0, 3).Value
ElseIf cell.Value = 4 Then
Dim Level4 As Single
cell.Offset(0, 3).Value = Level3 * cell.Offset(0, 2).Value
Level4 = cell.Offset(0, 3).Value
End If
Next cell
End Sub
Bookmarks