VBA for calculating total quantity in multi level Bill of material

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

this could take care of up to 20 levels

Please Login or Register  to view this content.

Excellent, this works really well.

Thankyou

Anytime, just mark it as solved

If level 1 has qty of 2, level 3 does not multoply correctly.
Please help

could yhou be more specific with a sample?

Capture.JPG

Hi
See attached clip

please how can i use vba to program or calculate bill of quantities. the first column is DESCRIPTION second is QUANTITY third is UNIT, fourth
RATE, fifth, AMOUNT. i want to program it in such a way that when we enter the quantity and the rate, it will authomatically calculate the amount. please if i get a video or code on how to do this i will be very happy
THANK U.

I know this is an old thread, but I thought this might help someone in the future.

As EPDM rightly points out, rcm's code works, but not in every case. A small edit seems to fix this bug:

Please Login or Register  to view this content.

My code is slightly different, but the main thing to notice is the additional line in the Else statement.

Cheers!