Subtracting same value from multiple cells (macro/VBA)

chrissy_v

New Member
Joined
Jul 18, 2022
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
Hey I'm super new to macros and am trying to figure it all out, and I'm not entirely sure if what I'm trying to do is possible.
Basically I have a large inventory list with current stock of parts on hand. I'm trying to make a macro/VBA to subtract '1' from multiple cells. (When I make an assembly I'm deducting '1' of certain parts from the current list thus updating the current stock on hand). I've tried to do =J2-1 (for example) but that doesn't do anything when I try to run it as a macro.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,502
Office Version
  1. 365
Platform
  1. Windows
OK, but what I am getting it is how do you know which rows you need to subtract from (3,5,9,10,11,12,13)?
What is the logic for determining with rows to include?
Are you matching a list somewhere to this list somewhere?

Remember, while this project is familiar to you, it is not to us. All we know is what you decide to share with us.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

chrissy_v

New Member
Joined
Jul 18, 2022
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
OK, but what I am getting it is how do you know which rows you need to subtract from (3,5,9,10,11,12,13)?
What is the logic for determining with rows to include?
Are you matching a list somewhere to this list somewhere?

Remember, while this project is familiar to you, it is not to us. All we know is what you decide to share with us.
All of the parts in the master list are organized by what type of part they are (cable, screw, washer, wire, etc).
I'm going through the master list and finding the parts used to make each product. I'm currently working on creating a list for each final product that has the row# for each part. Would it be better for me to finish making the lists and then come back here later when they're complete and post them?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,502
Office Version
  1. 365
Platform
  1. Windows
I think we kind of need to see all the important pieces and how the tie together in order to help you.
We don't necessarily need to see the complete list, just some small example of the different lists and how they tie together.

It sounds like somewhere you have some sort of "Product" list, which shows which parts are used in a particular "Product", right?
So, somewhere, you must be selecting some "Product", then looking up the parts that make up that products, and then going to your "Parts" listing, and reducing all those parts (that make up that product), right?

If that is correct, then it seems to me that we need to know/see the following three things:
1. Parts list
2. Products list
3. Product selection form

And then it is important for us to know what "triggers" this reduction in part numbers to run.
Is it when you are updating a particular field on the "Product selection" form, or are you envisioning some sort of button to manual kick-off the process that reduces the counts?
 

chrissy_v

New Member
Joined
Jul 18, 2022
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
I think we kind of need to see all the important pieces and how the tie together in order to help you.
We don't necessarily need to see the complete list, just some small example of the different lists and how they tie together.

It sounds like somewhere you have some sort of "Product" list, which shows which parts are used in a particular "Product", right?
So, somewhere, you must be selecting some "Product", then looking up the parts that make up that products, and then going to your "Parts" listing, and reducing all those parts (that make up that product), right?

If that is correct, then it seems to me that we need to know/see the following three things:
1. Parts list
2. Products list
3. Product selection form

And then it is important for us to know what "triggers" this reduction in part numbers to run.
Is it when you are updating a particular field on the "Product selection" form, or are you envisioning some sort of button to manual kick-off the process that reduces the counts?
Okay! I will post some screenshots here of examples of the lists. (I'm sorry I'm so hesitant to post the list in its entirety! I work for my dad and he has multiple patents on his products and how they are built, and I don't think he would appreciate it if I posted the entire list; I hope these samples will give you a better understanding).

Each of the products has its own sheet in the workbook that lists the parts used. The first screenshot is an example of a product sheet and the quantity of which parts are used.

The second screenshot is a small portion of the master list of stock of all parts organized alphabetically by part type.

What I started yesterday was creating a list of which cells in the master list of stock correspond to each product. (Third screenshot)

I'm wanting to make a button for each product that will trigger a deduction in the parts used for that product.
 

Attachments

  • Screen Shot 2022-07-19 at 7.53.06 AM.png
    Screen Shot 2022-07-19 at 7.53.06 AM.png
    79.8 KB · Views: 3
  • Screen Shot 2022-07-19 at 7.54.42 AM.png
    Screen Shot 2022-07-19 at 7.54.42 AM.png
    195 KB · Views: 3
  • Screen Shot 2022-07-19 at 8.05.25 AM.png
    Screen Shot 2022-07-19 at 8.05.25 AM.png
    11 KB · Views: 3

chrissy_v

New Member
Joined
Jul 18, 2022
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
I think we kind of need to see all the important pieces and how the tie together in order to help you.
We don't necessarily need to see the complete list, just some small example of the different lists and how they tie together.

It sounds like somewhere you have some sort of "Product" list, which shows which parts are used in a particular "Product", right?
So, somewhere, you must be selecting some "Product", then looking up the parts that make up that products, and then going to your "Parts" listing, and reducing all those parts (that make up that product), right?

If that is correct, then it seems to me that we need to know/see the following three things:
1. Parts list
2. Products list
3. Product selection form

And then it is important for us to know what "triggers" this reduction in part numbers to run.
Is it when you are updating a particular field on the "Product selection" form, or are you envisioning some sort of button to manual kick-off the process that reduces the counts?
Here is another screenshot of another assembly and the cells used. Most of the parts I only need to deduct 1, but on some parts I need to deduct more.
 

Attachments

  • Screen Shot 2022-07-19 at 9.03.32 AM.png
    Screen Shot 2022-07-19 at 9.03.32 AM.png
    18.4 KB · Views: 3

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,502
Office Version
  1. 365
Platform
  1. Windows
Okay! I will post some screenshots here of examples of the lists. (I'm sorry I'm so hesitant to post the list in its entirety! I work for my dad and he has multiple patents on his products and how they are built, and I don't think he would appreciate it if I posted the entire list; I hope these samples will give you a better understanding).
Any chance you can post using the tool I mentioned in my first reply?
This will allow me to easily copy/paste your data, so I can quickly recreate your structure on my side.
Otherwise, I will need to try to recreate it manually, which takes much longer, and is more prone to mistakes.

Each of the products has its own sheet in the workbook that lists the parts used. The first screenshot is an example of a product sheet and the quantity of which parts are used.
How are the sheets named?
Does the sheet name match the Product name exactly?

What I started yesterday was creating a list of which cells in the master list of stock correspond to each product. (Third screenshot)
This might not be necessary if your sheet name match the Product names. Then we may just be able to look things up.

I'm wanting to make a button for each product that will trigger a deduction in the parts used for that product.
Won't that be a lot of buttons?
How many products are we talking about?

We may be able to get away with a "one size fits all" button, but we would just then need to understand, when you click the button, how does it now which product to run against?
Is that being entered on a line somewhere?
Or do you want the VBA code to prompt them to enter the product to apply it against?
 

chrissy_v

New Member
Joined
Jul 18, 2022
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
Any chance you can post using the tool I mentioned in my first reply?
This will allow me to easily copy/paste your data, so I can quickly recreate your structure on my side.
Otherwise, I will need to try to recreate it manually, which takes much longer, and is more prone to mistakes.


How are the sheets named?
Does the sheet name match the Product name exactly?


This might not be necessary if your sheet name match the Product names. Then we may just be able to look things up.


Won't that be a lot of buttons?
How many products are we talking about?

We may be able to get away with a "one size fits all" button, but we would just then need to understand, when you click the button, how does it now which product to run against?
Is that being entered on a line somewhere?
Or do you want the VBA code to prompt them to enter the product to apply it against?
Sure I'll post them in my next reply.

The sheets are named by assembly/product. Some sheets are assemblies, and final products also have their own sheet including the assemblies used to create them. (so lets say one product has a face panel, back panel and all the stuff inside. the face panel has a sheet, the back panel has a sheet, the final product itself has a sheet that includes "front panel assembly" "back panel assembly" and all the parts used elsewhere. I hope that makes sense.)

Theres only 4 final products. So I would only need 4 buttons.

I'm wanting a code for each product/button (4 total), so when I make each product it can deduct the parts used.
 

chrissy_v

New Member
Joined
Jul 18, 2022
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
Any chance you can post using the tool I mentioned in my first reply?
This will allow me to easily copy/paste your data, so I can quickly recreate your structure on my side.
Otherwise, I will need to try to recreate it manually, which takes much longer, and is more prone to mistakes.


How are the sheets named?
Does the sheet name match the Product name exactly?


This might not be necessary if your sheet name match the Product names. Then we may just be able to look things up.


Won't that be a lot of buttons?
How many products are we talking about?

We may be able to get away with a "one size fits all" button, but we would just then need to understand, when you click the button, how does it now which product to run against?
Is that being entered on a line somewhere?
Or do you want the VBA code to prompt them to enter the product to apply it against?
Front panel example:
Inventory and Parts List 2022 with macros.xls
ABC
1CategoryDescriptionQTY
2AssemblyPCA controls board1
3Enclosure - PCAPCA faceplate1
4LED3-1/2 digit LED, 2V panel meter1
5NutNylon nut, 4-40 hex8
6OverlayLexan PCA overlay1
7Plug - Amp04-pin .100" closed-end plug1
8Plug - Amp10-pin .100" closed-end plug1
9Screw18-8 SS 4-40 x 1/4" philips pan screw4
10Screw18-8 SS 4-40 x 5/16" philips pan screw4
11StandoffNylon standoff, 1/4", 4-40, 1/4"8
Assy - PCA Front


Back panel example:
Inventory and Parts List 2022 with macros.xls
ABC
1CategoryDescriptionQTY
2BNCBNC panel connector1
3Enclosure - PCAPCA back panel1
4Nut18-80 SS 4-40 Star Nut2
5Plug - Amp02-pin .100" closed-end plug1
6PowerFused IEC power connector1
7Screw18-8 SS 4-40 x 5/16" philips flat screw2
8SwitchSPST rocker switch1
9Wire6" 20AWG black wire3
10Wire6" 20AWG green wire1
Assy - PCA Back


Final product example:
Inventory and Parts List 2022 with macros.xls
ABC
1CategoryDescriptionQTY
2AssemblyPCA back1
3AssemblyPCA front1
4AssemblyPCA main board1
5AssemblyOpto1
6CrankAluminum crank (new drawing)1
7CrankAnodized aluminum guide1
8CrankBearing1
9CrankBearing carrier1
10CrankHairpin cotter for 3/16"-1/4" shaft sizes1
11CrankRod end1
12CrankSS 3/16" x 7/8" Clevis pin1
13Enclosure - PCAPCA cover1
14Enclosure - PCAPCA frame - base1
15Enclosure - PCAPCA frame - top1
16Enclosure - PCAPCA frame - trunk1
17MotorAluminum motor bracket1
18Motor - PCABodine motor 115VAC 60hz1
PCA
 

chrissy_v

New Member
Joined
Jul 18, 2022
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
Any chance you can post using the tool I mentioned in my first reply?
This will allow me to easily copy/paste your data, so I can quickly recreate your structure on my side.
Otherwise, I will need to try to recreate it manually, which takes much longer, and is more prone to mistakes.


How are the sheets named?
Does the sheet name match the Product name exactly?


This might not be necessary if your sheet name match the Product names. Then we may just be able to look things up.


Won't that be a lot of buttons?
How many products are we talking about?

We may be able to get away with a "one size fits all" button, but we would just then need to understand, when you click the button, how does it now which product to run against?
Is that being entered on a line somewhere?
Or do you want the VBA code to prompt them to enter the product to apply it against?
Inventory and Parts List 2022 with macros.xls
ABCDEFGHIJ
17Capacitor4.7uF 50V ceramic capacitorMouserwww.mouser.com80-C350C475M5UKemetC350C475M5U5TA$2.0810
18Cleaning BrushLow-Scratch Tube Brush, 1/2" Diameter x 3" LongMcMaster-Carrwww.mcmaster.com7221T12**$4.00116
19CrankAluminum crank (new drawing)eMachineShopwww.eMachineShop.com331349.ems**$31.86112
20CrankAnodized aluminum guideeMachineShopwww.eMachineShop.comAlum Guide 111716RevA.ems**$50.00151
21CrankBall BearingIndustrial Devices770-935-4832FAF38PP**$21.70114
22CrankCrank Bearing HousingeMachineShopwww.eMachineShop.com257879.ems**$19.0319
23CrankHairpin cotter for 1/4"-3/8" shaft sizesMcMaster-Carrwww.mcmaster.com92391A130 **$10.002535
24CrankRod endMcMaster-Carrwww.mcmaster.com59915K21**$15.83123
25CrankSS 3/16" x 7/8" Clevis pinMcMaster-Carrwww.mcmaster.com92390A109**$9.491016
26Diode12V 1W zener diodeNewark InOnewww.newark.com95B4978Vishay1N4742A$0.42175
27Enclosure - PCAPCA back panelAccu Metals770-867-1124101911-Back **$30.43136
28Enclosure - PCAPCA coverAccu Metals770-867-1124101911A**$81.37122
29Enclosure - PCAPCA faceplateAccu Metals770-867-1124101911-F **$27.31122
30Enclosure - PCAPCA frame - baseRanger770-536-8031JS-05-0017**$105.0013
31Enclosure - PCALab Insert Top Plate- BLk AnodizeRanger770-536-803142817**$75.0016
32Enclosure - PCAPCA frame vertical plateRanger770-536-8031JS-05-0020**$97.8516
33Enclosure - SCMSS Right HingesMoore Industrial Hardwarewww.mooreindhardware.com190-2-4-2-1Paneloc$5.02117
34Enclosure - SCMSS Left HingesMoore Industrial Hardwarewww.mooreindhardware.com190-2-3-2-1Paneloc*$5.02118
35Enclosure - SCMSCM back paneleMachineShopwww.eMachineShop.com329788.ems*$38.47138
Inventory
Cell Formulas
RangeFormula
D17D17='Inventory and Parts List 2022 with macros.xls'!Mouser
D21D21='Inventory and Parts List 2022 with macros.xls'!IndustrialDevices
D23:D25D23='Inventory and Parts List 2022 with macros.xls'!McMaster
D26D26='Inventory and Parts List 2022 with macros.xls'!Newark
Named Ranges
NameRefers ToCells
IndustrialDevices=Suppliers!$B$12D21
McMaster=Suppliers!$B$15D23:D25
Mouser=Suppliers!$B$17D17
Newark=Suppliers!$B$18D26


And here's a small example of the master list with current stock
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,502
Office Version
  1. 365
Platform
  1. Windows
So, tell me if this is how it should work.

Let's say that you have a button for PCA. If you click the button, should it:
- go to the "PCA" sheet and loop through all the items listed
- find/match them up on the "Inventory" sheet (matching on the Category and Description fields)
- reduce the amount in column J on the "Inventory" sheet by the amount from the "Qty" column on the PCA sheet

Does that sound right?
 

Forum statistics

Threads
1,176,084
Messages
5,901,293
Members
434,886
Latest member
qazibelal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top