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.

 

[chrissy_v]

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!

 

[Joe4]

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]

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!

 

[Joe4]

Ah okay so it would be easier if I put them all in one sheet?

Thanks for the suggestions!

Yes, it would be.

 

[chrissy_v]

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]

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:

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:

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.

 

[chrissy_v]

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:

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:

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]

You are welcome.
Glad we were able to get a suitable solution for you.