Add / Subtract buttons for inventory

[Rafa1781]

Hi, I have very basic knowledge of excel so I really need some help right now. Needless to say I've been at this since 9 a.m.
I'm creating an inventory sheet (most of which is done). I have all my coulmns set up and labeled and all that good stuff. What I need is the ability to enter any number into any cell in column D and then click the add button and have that number added to an already existing quantity in column C.
Bottom line... I need a macro for an add button (column D) and a subtract button (column C). Both of which, when clicked affect inventory count in column C.
BTW, I already have my buttons set up where I want them.
Really hope someone can help me. Thanks in advance.

 

[GRTorres]

Hello Rafa1781, welcome to the board!

To verify: I want to place i number in column D. I can either add or subtract with a button click and it will affect column c appropriately, correct?

 

[GRTorres]

If this is what you are looking for use this:

Sub Add()
For i = 2 To Range("D" & Rows.Count).End(xlUp).Row
num = Range("D" & i).Value
If num <> "" Then
Range("D" & i).Offset(0, -1) = Range("D" & i).Offset(0, -1).Value + num
Range("D" & i).ClearContents
End If
Next i

End Sub

&

Sub subtract()

For i = 2 To Range("D" & Rows.Count).End(xlUp).Row
num = Range("D" & i).Value
If num <> "" Then
Range("D" & i).Offset(0, -1) = Range("D" & i).Offset(0, -1).Value - num
Range("D" & i).ClearContents
End If
Next i

End Sub

Assign each macro to the appropriate buttons, if scenario above matches.

 

[Rafa1781]

Hi GRTorres, thanks for the welcome and for your help. Please bear with me, as I mentioned before. I have very basic knowledge of excel. Let me tell what I did step by step with the add code you gave.
I right clicked on the button I created in cell E2 and selected view code. That took to MVB. I removed what was there and pasted your code then closed MVB. I deselected design mode. In cell D3 I have a quantity of 1333. So what I thought should happen is that I could put (for example) 5 in E3, click the button in E2 with your code and it would add 5 to the 1333 in D3. Nothing happened at all. What did I do wrong?

 

[GRTorres]

I don't believe you have anything wrong, only that I don't understand how you have your spreadsheet set up.

Can you please explain further what you want it to do and where you are inputting data and where it should affect your spreadsheet?

 

[Rafa1781]

Column A = Inventory ID
Column B = Name
Column C = Description
Column D = Quantity in stock
Column E = Received
Column F = Deduct for assembly

Idealy what I would like to have happen is (for example) , let say I have 200 in D. I hard punch 100 into E when new inventory comes in, press enter, click button and it updates column D to 300 and the 100 I hard punched into E disappears. Likewise for Column F but subtract.
I have all that setup along with buttons but I cant get the codes right for the life of me.

 

[GRTorres]

Here you go:

Add

Sub Add()

For i = 3 To Range("E" & Rows.Count).End(xlUp).Row
num = Range("E" & i).Value
If num <> "" Then
Range("E" & i).Offset(0, -1) = Range("E" & i).Offset(0, -1).Value + num
Range("E" & i).ClearContents
End If
Next i

End Sub

Subtract

Sub subtract()

For i = 3 To Range("F" & Rows.Count).End(xlUp).Row
num = Range("F" & i).Value
If num <> "" Then
Range("F" & i).Offset(0, -2) = Range("F" & i).Offset(0, -2).Value - num
Range("F" & i).ClearContents
End If
Next i

End Sub

 

[Rafa1781]

Dude I feel like a real idiot right now. It's still now working, has to be something Im doing wrong. The button is an activeX control. I right click to view code, enter your code. Exit, try it and nothing happens.
I have 1333 in D, I enter 10 in E, hit enter and arrow up to the cell with the 10, click the button, and nothing. Wont be surprised if you give up on me. Thanks for your efforts and time.

 

[GRTorres]

Macros and everything are enabled? It is not giving you an error or anything?

 

[GRTorres]

Is your design mode turned off? Meaning, where you created your button, make sure to turn off design mode.