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]

Welcome to the Board!

Where are all these values, that you want to subtract one from, in relation to each other?

I think it would be very beneficial to us to see a sample of your data structure, and your expected output.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[My Aswer Is This]

We may need more details but with just your example it would be Range("J2") = Range("J2") - 1

 

[chrissy_v]

Welcome to the Board!

Where are all these values, that you want to subtract one from, in relation to each other?

I think it would be very beneficial to us to see a sample of your data structure, and your expected output.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thank you!
So all of the parts the company uses are in one master list. All of the QOH are in the J column, and there's 254 rows in total. However depending on what we produce there will be different parts used. (for example one product could be using J2,6,10,8,5,etc and a different product could be J2,6,7,9,12,etc). Most of the final products we make use a lot of the same parts however there are some variations.
So my goal is to make multiple buttons to run macros for each final product that will subtract '1' from each part used, giving a running QOH.

 

[chrissy_v]

We may need more details but with just your example it would be Range("J2") = Range("J2") - 1

Ahhh thank you! To do multiple cells at once would I code it like Range("J2, J4, J5") = Range("J2, J4, J5") - 1 ?

 

[Joe4]

Ahhh thank you! To do multiple cells at once would I code it like Range("J2, J4, J5") = Range("J2, J4, J5") - 1 ?

Nope, that would not work. You cannot write it like that.
If you had a range that just contained cells J2, J4, and J5, you could loop through each cell in the range and apply the subtraction.

How is it determined which cells to include?
Is there any logic regarding which cells should be included and which should not (i.e. maybe something contained in another cell in the same row)?

That is why I was really hoping that you would post a sample of your data, that maybe if we saw it, it would be obvious to us how can identify those cells.

 

[chrissy_v]

Nope, that would not work. You cannot write it like that.
If you had a range that just contained cells J2, J4, and J5, you could loop through each cell in the range and apply the subtraction.

How is it determined which cells to include?
Is there any logic regarding which cells should be included and which should not (i.e. maybe something contained in another cell in the same row)?

That is why I was really hoping that you would post a sample of your data, that maybe if we saw it, it would be obvious to us how can identify those cells.

Inventory and Parts List 2022 with macros.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Category	Description	Supplier	Supplier Contact	Supplier Number	Manufacturer	Manufacturer Num.	Price	Per	Stock	Value
2	BNC	BNC bulkhead connector	Mouser	www.mouser.com	523-31-10-RFX	Amphenol	31-10-RFX	$2.98	1	45	$134.10
3	BNC	BNC panel connector	Mouser	www.mouser.com	523-31-203RFX	Amphenol	31-203-RFX	$4.72	1	6	$28.32
4	Bumper Feet - PCA	Threaded-Stud Bumper, SBR, 8-32 x 3/8"	McMaster-Carr	www.mcmaster.com	9541K2	*	*	$15.28	25	33	$20.17
5	Cable	Liquid-tight cable connectors / strain reliefs	Industrial Devices	770-935-4832	PG9	ACI	106177	$1.00	1	151	$151.00
6	Cable - BNC	BNC to BNC 2 ft. CALRAD	Cable Depot	www.cabledepot.com	BNC-BNC-RG59-2	*	*	$5.75	1	30	$172.50
7	Cable - Power	Power cable 2.5m INTL 10A	Mouser	www.mouser.com	562-363012	Qualtek	363012-D01	$8.10	1	3	$24.30
8	Cable - Power	Power cable 79" 18AWG NEMA 10A	Mouser	www.mouser.com	562-311007-01	Qualtek	311007-01	$4.65	1	61	$283.65
9	Cable - Power	Power cable 90" 18AWG SVT 10A	Newark InOne	www.newark.com	98K6011	Volex	17250-B-1-10	$5.27	1	17	$89.59
10	Cable - Ribbon	06" 20-pin 0.1" ribbon cable	Mouser	www.mouser.com	517-1M-1010-020-6	3M	1M-1010-020-3365-006.0-00-AB-00-0	$8.50	1	68	$578.00
11	Cable - Ribbon	06" 60-pin 0.1" ribbon cable	Mouser	www.mouser.com	517-1M-1010-060-6	3M	1M-1010-060-3365-006.0-00-AB-00-0	$13.25	1	72	$954.00
12	Cable - Ribbon	12" 16-pin 0.1" ribbon cable	Mouser	www.mouser.com	517-1M-1010-016-12	3M	1M-1010-016-3365-012.0-00-AB-00-0	$8.72	1	55	$479.60
13	Cable - Ribbon	18" 16-pin 0.1" ribbon cable	Mouser	www.mouser.com	517-1M-1010-016-18	3M	1M-1010-016-3365-018.0-00-AB-00-0	$9.38	1	52	$487.76
14	Capacitor	0.1uF 50V ceramic capacitor	Digi-Key	www.mouser.com	80-C317C104M5U	Kemet	C317C104M5U5TA	$0.13	1	104	$13.52
15	Capacitor	1uF 50V ceramic capacitor	Digi-Key	www.mouser.com	80-C330C105M5U	Kemet	C330C105M5U5TA	$0.30	1	925	$279.35
16	Capacitor	3300pF 50V ceramic capacitor	Mouser	www.mouser.com	80-C315C332K5R	Kemet	C315C332K5R5TA	$0.14	1	25	$3.50
17	Capacitor	4.7uF 50V ceramic capacitor	Mouser	www.mouser.com	80-C350C475M5U	Kemet	C350C475M5U5TA	$2.08	1	0	$0.00
18	Cleaning Brush	Low-Scratch Tube Brush, 1/2" Diameter x 3" Long	McMaster-Carr	www.mcmaster.com	7221T12	*	*	$4.00	1	16	$64.00
Inventory

Cell Formulas
Range	Formula
D2:D3,D7:D8,D10:D17	D2	='Inventory and Parts List 2022 with macros.xls'!Mouser
D4	D4	='Inventory and Parts List 2022 with macros.xls'!McMaster
D5	D5	='Inventory and Parts List 2022 with macros.xls'!IndustrialDevices
D9	D9	='Inventory and Parts List 2022 with macros.xls'!Newark
K2:K18	K2	=(H2*J2)/I2

Named Ranges
Name	Refers To	Cells
BNCbulkhead	=Inventory!$H$2	K2
BNCcable	=Inventory!$H$6	K6
BNCpanel	=Inventory!$H$3	K3
Cap0.1uF	=Inventory!$H$14	K14
Cap1uf	=Inventory!$H$15	K15
Cap3300pF	=Inventory!$H$16	K16
Cap4.7uF	=Inventory!$H$17	K17
IndustrialDevices	=Suppliers!$B$12	D5
INTLcable	=Inventory!$H$7	K7
McMaster	=Suppliers!$B$15	D4
Mouser	=Suppliers!$B$17	D10:D17, D7:D8, D2:D3
NEMAcable	=Inventory!$H$8	K8
Newark	=Suppliers!$B$18	D9
RibbonDisplay	=Inventory!$H$10	K10
RibbonLeft	=Inventory!$H$13	K13
RibbonMain	=Inventory!$H$11	K11
RibbonRight	=Inventory!$H$12	K12
StrainRelief	=Inventory!$H$5	K5
SVTcable	=Inventory!$H$9	K9

I have included a small sample here. All of the parts in the master list are organized by what type of part they are, so there is no "rhyme or reason" for what I'm trying to do (because each final product uses all sorts of different parts).

I'm currently working on creating a list of parts with all corresponding cells for each final product.

 

[chrissy_v]

Nope, that would not work. You cannot write it like that.
If you had a range that just contained cells J2, J4, and J5, you could loop through each cell in the range and apply the subtraction.

How is it determined which cells to include?
Is there any logic regarding which cells should be included and which should not (i.e. maybe something contained in another cell in the same row)?

That is why I was really hoping that you would post a sample of your data, that maybe if we saw it, it would be obvious to us how can identify those cells.

They are all in the same column, but will have a wide variety of rows

 

[Joe4]

So, where exactly is the subtraction happening?
Can you walk us through an actual example, based on your data sample?

 

[chrissy_v]

So, where exactly is the subtraction happening?
Can you walk us through an actual example, based on your data sample?

In the J column (the current stock on hand).
(This example won't be completely accurate as there's over 250 rows) but let's say one product uses parts J3, J5, J9, J10, J11, J12, J13. So I would need to subtract '1' from those cells