VBA Loop to subtract values from stock quantity

I am trying to create an inventory sheet that has buttons to subtract a specified quantity from a stock quantity. There are different "builds" that require different inventory items, and I would like to be able to subtract the amount of inventory from the stock quantity for the specified build (See below):

[Part #] [Description] [Stock Qty] [Build 1] [Build 2]
1 _______ Screw _______ 20 _______ 2 ______ 1
2 _______ Bolt ________ 20 ________ 1
3 _______ Pipe ________ 10 _________________ 2

So if I performed Build 1, there would be 18 screws, 19 bolts, and 10 pipes left. If I performed build 2 (from the same starting quantity), there would be 19 screws, 20 bolts, and 8 pipes. I have written out code for each item individually by referencing the exact cell using the following code:

Formula:

Please Login or Register  to view this content.

The only problem is that there are over 100 inventory items on my spreadsheet and each time I change the location of something, I have to go in and update every cell reference (there are 6 "builds", so this is a lot). I am thinking there would be a way to loop through checking to see if there is anything in the "build" columns (D,E above) and somehow index that row to specify the correct stock quantity cell, but I'm a noob with VBA and can't seem to get anything to work.

Thanks in advance for any guidance!

Hi Robert,

Thanks for the response, the only problem is that these builds are constantly being performed. I.e., I may need to subtract five quantities at once, and be able to add in more inventory as it comes in and subtract more build quantities as the are performed.

I figured out what I was trying to do. While the actual spreadsheet has many more items and products that are being built ("Builds"), here is a simple version that is based off of the example above:

Please Login or Register  to view this content.

This code would be assigned to a button for "Build 1". I then would have another set of code assigned to another button for "Build 2", which would reference column 5 in place of column for in the above code.

I added in an input box since sometime we were building multiple quantities of a product at the same time. I hope someone else finds this useful!