I am trying to manage a list of inventory items using Excel 2016. I have three end products, each with its own Bill of Materials, and I have the relevant information all on one worksheet (Inventory Part, current inventory quantity, and quantity of said part in each of the 3 BOMs).
Say I am trying to create a build of Product 1. I want to click a button that will run through the column of parts used to assemble Product 1, subtract each quantity of each part from the corresponding inventory quantity, and replace the current inventory quantity with this difference to reflect the new inventory quantity.
I initially tried writing a VBA script to accomplish this but have been having difficulty with the syntax and getting it to replace the value of a cell with a new formula. I do not know how to specify the selection of cells that I am targeting. I am new to VBA and coding using Excel macros, so I do not know whether trying to record a macro to do this would be easier. Any help is very much appreciated!
Say I am trying to create a build of Product 1. I want to click a button that will run through the column of parts used to assemble Product 1, subtract each quantity of each part from the corresponding inventory quantity, and replace the current inventory quantity with this difference to reflect the new inventory quantity.
I initially tried writing a VBA script to accomplish this but have been having difficulty with the syntax and getting it to replace the value of a cell with a new formula. I do not know how to specify the selection of cells that I am targeting. I am new to VBA and coding using Excel macros, so I do not know whether trying to record a macro to do this would be easier. Any help is very much appreciated!
