Using SumIf & VBA to Account for Multiple Instances of Same Part # in Bill of Materials

Hello,

Attaching a sample workbook to give an idea of what I am trying to accomplish - trying to use the SumIf function via VBA to help automate a build / bill of materials (BOM) process that allows us to figure out how many of each part we need to order for an upcoming build. In our BOM, we use the same part in different subassemblies, meaning the same part may show up multiple times throughout the entire BOM with a different pick quantity for each subassembly. i.e. we might use 1 given resistor in 1 subassembly and 2 more in a separate subassembly for the same build. Reason we would like to automate this is so that when we have future orders, we want to enter Build Quantity and have the sheet display how many of each unique part we need to order, accounting for all instances of each part across the subassemblies.

Below in some word math:
total BOM quantity of a specific part number = pick quantity of said part in subassembly 1 + pick quantity of same part in subassembly 2 + ...

number to order = total BOM quantity of said part number - current inventory quantity on hand

I am using a For loop to iterate through the part numbers in a given BOM and understand (I think) the SumIf function is what I need. But then once all instances of a part have been found and the # to order is determined, as the loop iterates through the remaining part numbers it will also need to skip the lines of part numbers that have already been accounted for above. Does this make sense? Attaching sample workbook for reference. Thank you in advance for any feedback / direction - greatly appreciated.

Is it simply like this?

Please Login or Register  to view this content.