Subtracting same value from multiple cells (macro/VBA)

[chrissy_v]

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]

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.

 

[chrissy_v]

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]

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]

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.

 

[chrissy_v]

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.

 

[Joe4]

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]

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]

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
	A	B	C
1	Category	Description	QTY
2	Assembly	PCA controls board	1
3	Enclosure - PCA	PCA faceplate	1
4	LED	3-1/2 digit LED, 2V panel meter	1
5	Nut	Nylon nut, 4-40 hex	8
6	Overlay	Lexan PCA overlay	1
7	Plug - Amp	04-pin .100" closed-end plug	1
8	Plug - Amp	10-pin .100" closed-end plug	1
9	Screw	18-8 SS 4-40 x 1/4" philips pan screw	4
10	Screw	18-8 SS 4-40 x 5/16" philips pan screw	4
11	Standoff	Nylon standoff, 1/4", 4-40, 1/4"	8
Assy - PCA Front

Back panel example:

Inventory and Parts List 2022 with macros.xls
	A	B	C
1	Category	Description	QTY
2	BNC	BNC panel connector	1
3	Enclosure - PCA	PCA back panel	1
4	Nut	18-80 SS 4-40 Star Nut	2
5	Plug - Amp	02-pin .100" closed-end plug	1
6	Power	Fused IEC power connector	1
7	Screw	18-8 SS 4-40 x 5/16" philips flat screw	2
8	Switch	SPST rocker switch	1
9	Wire	6" 20AWG black wire	3
10	Wire	6" 20AWG green wire	1
Assy - PCA Back

Final product example:

Inventory and Parts List 2022 with macros.xls
	A	B	C
1	Category	Description	QTY
2	Assembly	PCA back	1
3	Assembly	PCA front	1
4	Assembly	PCA main board	1
5	Assembly	Opto	1
6	Crank	Aluminum crank (new drawing)	1
7	Crank	Anodized aluminum guide	1
8	Crank	Bearing	1
9	Crank	Bearing carrier	1
10	Crank	Hairpin cotter for 3/16"-1/4" shaft sizes	1
11	Crank	Rod end	1
12	Crank	SS 3/16" x 7/8" Clevis pin	1
13	Enclosure - PCA	PCA cover	1
14	Enclosure - PCA	PCA frame - base	1
15	Enclosure - PCA	PCA frame - top	1
16	Enclosure - PCA	PCA frame - trunk	1
17	Motor	Aluminum motor bracket	1
18	Motor - PCA	Bodine motor 115VAC 60hz	1
PCA

 

[chrissy_v]

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
	A	B	C	D	E	F	G	H	I	J
17	Capacitor	4.7uF 50V ceramic capacitor	Mouser	www.mouser.com	80-C350C475M5U	Kemet	C350C475M5U5TA	$2.08	1	0
18	Cleaning Brush	Low-Scratch Tube Brush, 1/2" Diameter x 3" Long	McMaster-Carr	www.mcmaster.com	7221T12	*	*	$4.00	1	16
19	Crank	Aluminum crank (new drawing)	eMachineShop	www.eMachineShop.com	331349.ems	*	*	$31.86	1	12
20	Crank	Anodized aluminum guide	eMachineShop	www.eMachineShop.com	Alum Guide 111716RevA.ems	*	*	$50.00	1	51
21	Crank	Ball Bearing	Industrial Devices	770-935-4832	FAF38PP	*	*	$21.70	1	14
22	Crank	Crank Bearing Housing	eMachineShop	www.eMachineShop.com	257879.ems	*	*	$19.03	1	9
23	Crank	Hairpin cotter for 1/4"-3/8" shaft sizes	McMaster-Carr	www.mcmaster.com	92391A130	*	*	$10.00	25	35
24	Crank	Rod end	McMaster-Carr	www.mcmaster.com	59915K21	*	*	$15.83	1	23
25	Crank	SS 3/16" x 7/8" Clevis pin	McMaster-Carr	www.mcmaster.com	92390A109	*	*	$9.49	10	16
26	Diode	12V 1W zener diode	Newark InOne	www.newark.com	95B4978	Vishay	1N4742A	$0.42	1	75
27	Enclosure - PCA	PCA back panel	Accu Metals	770-867-1124	101911-Back	*	*	$30.43	1	36
28	Enclosure - PCA	PCA cover	Accu Metals	770-867-1124	101911A	*	*	$81.37	1	22
29	Enclosure - PCA	PCA faceplate	Accu Metals	770-867-1124	101911-F	*	*	$27.31	1	22
30	Enclosure - PCA	PCA frame - base	Ranger	770-536-8031	JS-05-0017	*	*	$105.00	1	3
31	Enclosure - PCA	Lab Insert Top Plate- BLk Anodize	Ranger	770-536-8031	42817	*	*	$75.00	1	6
32	Enclosure - PCA	PCA frame vertical plate	Ranger	770-536-8031	JS-05-0020	*	*	$97.85	1	6
33	Enclosure - SCM	SS Right Hinges	Moore Industrial Hardware	www.mooreindhardware.com	190-2-4-2-1	Paneloc		$5.02	1	17
34	Enclosure - SCM	SS Left Hinges	Moore Industrial Hardware	www.mooreindhardware.com	190-2-3-2-1	Paneloc	*	$5.02	1	18
35	Enclosure - SCM	SCM back panel	eMachineShop	www.eMachineShop.com	329788.ems		*	$38.47	1	38
Inventory

Cell Formulas
Range	Formula
D17	D17	='Inventory and Parts List 2022 with macros.xls'!Mouser
D21	D21	='Inventory and Parts List 2022 with macros.xls'!IndustrialDevices
D23:D25	D23	='Inventory and Parts List 2022 with macros.xls'!McMaster
D26	D26	='Inventory and Parts List 2022 with macros.xls'!Newark

Named Ranges
Name	Refers To	Cells
IndustrialDevices	=Suppliers!$B$12	D21
McMaster	=Suppliers!$B$15	D23:D25
Mouser	=Suppliers!$B$17	D17
Newark	=Suppliers!$B$18	D26

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

 

[Joe4]

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?