Button for adding / subtracting different columns then clearing contents.

[jochoa]

Hey everybody, I need some assistance. I've been trying to come up with a command button to be able to add / subtract inventory from the current inventory.

I have a rough example of some random data below. I'm looking to essentially for a function to allow Range C = Range C + Range D - Range E. I was able to do it for a single line item, but i have a parts table of over 2000 items, and have been having a hard time with making something for a running total.

After either adding/subtracting inventory, i would like to use the clear contents function to clear all the items in Range D and Range E. I'm not sure if I can combine both the addition and subtraction into the same command button. If not, I can create two command buttons, one for Qty In. and one for Qty Out. Any help would be appreciated.

 

[SanjayGMusafir]

After either adding/subtracting inventory, i would like to use the clear contents function to clear all the items in Range D and Range E. I'm not sure if I can combine both the addition and subtraction into the same command button. If not, I can create two command buttons, one for Qty In. and one for Qty Out. Any help would be appreciated.

Before populating a command button, you will need the following -

1. First, a helper column that can show current net quantity, where in you can have formula as mentioned above - Range F = Range C + Range D - Range E
2. Then you can start with recording macro - where in you can copy the Net Quantity column and paste values in Quantity column
3. Then, you can select In and Out columns and clear the values
4. Stop Macro recording
5. Done

Now you can assign that Macro to any command Button.

 

[BigBeachBananas]

How about this?

UNTESTED CODE- Try this on a COPY of your workbook.

Sub UpdateInventory()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to the name of your worksheet
 
    Dim i As Long
    For i = 2 To ws.Cells(Rows.Count, "C").End(xlUp).Row ' Assuming data starts on row 2, adjust as needed
        ws.Cells(i, "C").Value = ws.Cells(i, "C").Value + ws.Cells(i, "D").Value - ws.Cells(i, "E").Value

        ws.Cells(i, "D").Value = ""
        ws.Cells(i, "E").Value = ""
    Next i
End Sub

Alternative to a command button, you can use a shape and assign the macro to it.

 

[jochoa]

How about this?

UNTESTED CODE- Try this on a COPY of your workbook.

Sub UpdateInventory()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to the name of your worksheet
 
    Dim i As Long
    For i = 2 To ws.Cells(Rows.Count, "C").End(xlUp).Row ' Assuming data starts on row 2, adjust as needed
        ws.Cells(i, "C").Value = ws.Cells(i, "C").Value + ws.Cells(i, "D").Value - ws.Cells(i, "E").Value

        ws.Cells(i, "D").Value = ""
        ws.Cells(i, "E").Value = ""
    Next i
End Sub

Alternative to a command button, you can use a shape and assign the macro to it.

It looks like this worked. Thanks for the help!