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.
 

chrissy_v

New Member
Joined
Jul 18, 2022
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
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?
Exactly! However it's a *tiny* bit more complicated than that.
-go to the "PCA" sheet loop through all the items listed
However on the "PCA" sheet there are a few line items for "PCA front", "PCA back" and "PCA main"
These items listed under "PCA" sheet are actually assemblies and they have their own sheet as well (with the supplies and quantities used to make those assemblies.)
If it's not possible to loop through multiple sheets I could combine them all into one new sheet.

But otherwise yes that's exactly what I'm trying to do!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,502
Office Version
  1. 365
Platform
  1. Windows
Exactly! However it's a *tiny* bit more complicated than that.
-go to the "PCA" sheet loop through all the items listed
However on the "PCA" sheet there are a few line items for "PCA front", "PCA back" and "PCA main"
These items listed under "PCA" sheet are actually assemblies and they have their own sheet as well (with the supplies and quantities used to make those assemblies.)
If it's not possible to loop through multiple sheets I could combine them all into one new sheet.

But otherwise yes that's exactly what I'm trying to do!
Yikes! Those "tiny" complications can really add to the complexity.
And there are usually other issues/exceptions which present themselves along the way (i.e. what happens if you don't have enough items in stock, or adding new products, etc).
One might argue that this may be a bit much than can be expected from free help, and is really falling more into "Consulting Project" territory.

Have you look at the pre-made Excel Inventory Templates that are available out there (that you can find with a simple Google search)?
It might be easier to use something like that then try to "recreate the wheel".

Also note that what you are describing is really a relational datbase, where relational database programs (like Microsoft Access or SQL) usually work better (because that is what they were designed for, where Excel was not). Excel recently came out with Power Query, which allows you to use relational database type concepts in Excel. We have a forum for that here: Power Tools
 

chrissy_v

New Member
Joined
Jul 18, 2022
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
Yikes! Those "tiny" complications can really add to the complexity.
And there are usually other issues/exceptions which present themselves along the way (i.e. what happens if you don't have enough items in stock, or adding new products, etc).
One might argue that this may be a bit much than can be expected from free help, and is really falling more into "Consulting Project" territory.

Have you look at the pre-made Excel Inventory Templates that are available out there (that you can find with a simple Google search)?
It might be easier to use something like that then try to "recreate the wheel".

Also note that what you are describing is really a relational datbase, where relational database programs (like Microsoft Access or SQL) usually work better (because that is what they were designed for, where Excel was not). Excel recently came out with Power Query, which allows you to use relational database type concepts in Excel. We have a forum for that here: Power Tools
Ah okay so it would be easier if I put them all in one sheet?

Thanks for the suggestions!
 

chrissy_v

New Member
Joined
Jul 18, 2022
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
Yes, it would be.
If I put it all in one sheet would it still be difficult to have it loop through and change the QOH?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,502
Office Version
  1. 365
Platform
  1. Windows
OK, I made code that updates the Inventory sheet. I made it generic so it should work for any Product (and this code is re-usable for all Products).
So, if you have 4 Products (and 4 Product sheets, one for each one), here is how you would set it up.

First, here is the general code to update the Inventory sheet:
VBA Code:
Private Sub ReduceInventory(wsPrd As Worksheet)

    Dim wsInv As Worksheet
    Dim lrP As Long
    Dim lrI As Long
    Dim rP As Long
    Dim rI As Long
    Dim cat As String
    Dim des As String
    Dim qty As Long
    
'   Designate Inventory worksheets
    Set wsInv = Sheets("Inventory")
    
    Application.ScreenUpdating = False
    
'   Find last row of data on Product and Inventory sheets
    lrP = wsPrd.Cells(Rows.Count, "A").End(xlUp).Row
    lrI = wsInv.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows on Product sheet, starting with row 2
    For rP = 2 To lrP
'       Get Category, Description, and Quantity from row
        cat = wsPrd.Cells(rP, "A")
        des = wsPrd.Cells(rP, "B")
        qty = wsPrd.Cells(rP, "C")
'       Loop through all rows on Inventory sheet until you find the match
        For rI = 2 To lrI
'           See if columns A and B match
            If wsInv.Cells(rI, "A") = cat And wsInv.Cells(rI, "B") = des Then
'               Subtract quantity from column J
                wsInv.Cells(rI, "J").Value = wsInv.Cells(rI, "J").Value - qty
                Exit For
            End If
'           Check to see if not found
            If rI = lrI Then
                MsgBox "Cannot find entry for " & cat & "/" & des & " on Inventory sheet", vbOKOnly
            End If
        Next rI
    Next rP
        
    Application.ScreenUpdating = True
    
    MsgBox "Inventory update finished!", vbOKOnly
    
End Sub

Then, we want the section of code that calls this code, feeding in the appropriate Product sheet.
So, for the PCA sheet, the code to attach to your button would look like this:
VBA Code:
Sub ProcessPCA()
'   Call ReduceInventory code and pass "PCA" sheet reference
    Call ReduceInventory(Sheets("PCA"))
End Sub

And you would have similar procedures for the other products, just changing the procedure name and sheet name in that code above.
 
Solution

chrissy_v

New Member
Joined
Jul 18, 2022
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
OK, I made code that updates the Inventory sheet. I made it generic so it should work for any Product (and this code is re-usable for all Products).
So, if you have 4 Products (and 4 Product sheets, one for each one), here is how you would set it up.

First, here is the general code to update the Inventory sheet:
VBA Code:
Private Sub ReduceInventory(wsPrd As Worksheet)

    Dim wsInv As Worksheet
    Dim lrP As Long
    Dim lrI As Long
    Dim rP As Long
    Dim rI As Long
    Dim cat As String
    Dim des As String
    Dim qty As Long
   
'   Designate Inventory worksheets
    Set wsInv = Sheets("Inventory")
   
    Application.ScreenUpdating = False
   
'   Find last row of data on Product and Inventory sheets
    lrP = wsPrd.Cells(Rows.Count, "A").End(xlUp).Row
    lrI = wsInv.Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all rows on Product sheet, starting with row 2
    For rP = 2 To lrP
'       Get Category, Description, and Quantity from row
        cat = wsPrd.Cells(rP, "A")
        des = wsPrd.Cells(rP, "B")
        qty = wsPrd.Cells(rP, "C")
'       Loop through all rows on Inventory sheet until you find the match
        For rI = 2 To lrI
'           See if columns A and B match
            If wsInv.Cells(rI, "A") = cat And wsInv.Cells(rI, "B") = des Then
'               Subtract quantity from column J
                wsInv.Cells(rI, "J").Value = wsInv.Cells(rI, "J").Value - qty
                Exit For
            End If
'           Check to see if not found
            If rI = lrI Then
                MsgBox "Cannot find entry for " & cat & "/" & des & " on Inventory sheet", vbOKOnly
            End If
        Next rI
    Next rP
       
    Application.ScreenUpdating = True
   
    MsgBox "Inventory update finished!", vbOKOnly
   
End Sub

Then, we want the section of code that calls this code, feeding in the appropriate Product sheet.
So, for the PCA sheet, the code to attach to your button would look like this:
VBA Code:
Sub ProcessPCA()
'   Call ReduceInventory code and pass "PCA" sheet reference
    Call ReduceInventory(Sheets("PCA"))
End Sub

And you would have similar procedures for the other products, just changing the procedure name and sheet name in that code above.
AAHHH THANK YOU SO MUCH IT WORKED!!!!! ♡
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,502
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad we were able to get a suitable solution for you.
 

Forum statistics

Threads
1,176,085
Messages
5,901,295
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