# VBA for calculating total quantity in multi level Bill of material

1. ## 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

2. ## Re: VBA for calculating total quantity in multi level Bill of material

this could take care of up to 20 levels

``Please Login or Register  to view this content.``

3. ## Re: VBA for calculating total quantity in multi level Bill of material

Excellent, this works really well.

Thankyou

4. ## Re: VBA for calculating total quantity in multi level Bill of material

Anytime, just mark it as solved

5. ## Re: VBA for calculating total quantity in multi level Bill of material

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

6. ## Re: VBA for calculating total quantity in multi level Bill of material

could yhou be more specific with a sample?

7. ## Re: VBA for calculating total quantity in multi level Bill of material

Capture.JPG

Hi
See attached clip

8. ## Re: VBA for calculating total quantity in multi level Bill of material

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.

9. ## Re: VBA for calculating total quantity in multi level Bill of material

gabriel boahene welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

10. ## Re: VBA for calculating total quantity in multi level Bill of material

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!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1