Button for adding / subtracting different columns then clearing contents.

jochoa

New Member
Joined
Oct 2, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.

1696280343426.png
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Last edited:
Upvote 0
How about this?

UNTESTED CODE- Try this on a COPY of your workbook.
VBA Code:
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.
 
Upvote 0
Solution
How about this?

UNTESTED CODE- Try this on a COPY of your workbook.
VBA Code:
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!
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,249
Members
452,623
Latest member
Techenthusiast

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
Back
Top