+ Reply to Thread
Results 1 to 10 of 10

VBA for calculating total quantity in multi level Bill of material

  1. #1
    Registered User
    Join Date
    04-07-2013
    Location
    belfast
    MS-Off Ver
    Excel 2010
    Posts
    26

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    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. #3
    Registered User
    Join Date
    04-07-2013
    Location
    belfast
    MS-Off Ver
    Excel 2010
    Posts
    26

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

    Excellent, this works really well.

    Thankyou

  4. #4
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

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

    Anytime, just mark it as solved

  5. #5
    Registered User
    Join Date
    06-19-2014
    Location
    Western Cape, SA
    MS-Off Ver
    2013
    Posts
    2

    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.
    Please help

  6. #6
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

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

    could yhou be more specific with a sample?

  7. #7
    Registered User
    Join Date
    06-19-2014
    Location
    Western Cape, SA
    MS-Off Ver
    2013
    Posts
    2

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

    Capture.JPG

    Hi
    See attached clip

  8. #8
    Registered User
    Join Date
    05-05-2018
    Location
    accra, ghana
    MS-Off Ver
    excell 2016
    Posts
    1

    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. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    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.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Registered User
    Join Date
    12-17-2020
    Location
    Texas
    MS-Off Ver
    365
    Posts
    1

    Lightbulb 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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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