Subtract cell from another and replace it with the difference

Rafalski

New Member
Joined
May 28, 2018
Messages
4
Hi Guys,

I would like to create a stock list of items and need some help. What I'm looking to achieve is:
Column A will have list of items, column B current quantity in stock & in column C should have input on quantity used.
I need a formula or VBA code that will deduct B3 from B2 and then replace B2 with the difference. This should apply to cells range B2:C50


Thanks
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Post a simple example of what you have and what you want it to look like. Just a couple of lines. I am a bit confused as you refer to column C and then column B. Which one is being subtracted.
 
Upvote 0
Hi Guys,

I would like to create a stock list of items and need some help. What I'm looking to achieve is:
Column A will have list of items, column B current quantity in stock & in column C should have input on quantity used.
I need a formula or VBA code that will deduct B3 from B2 and then replace B2 with the difference. This should apply to cells range B2:C50


Thanks

Sorry, my mistake. I want to deduct C2 from B2 and then replace B2 with its difference (B2-C2).
So every time I input value in C2 and hit enter it will re-calculate value in B2 (maybe command button would be better).
 
Upvote 0
Put this code in the Worksheet_Change Event and not in a module

Code:
Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim thisrow As Long
    If Target.Column = 3 Then
        thisrow = Target.Row
        Range("B" & thisrow) = Range("B" & thisrow) - Range("C" & thisrow)
    End If
End Sub

Open your VBE, click on the worksheet you wish to have this code in and place it the worksheet_change event.
 
Upvote 0
Above code is working fine - thank you alansidman.

I would like to get two more things added one if possible:
1. clear cell content (the one where we input number, in column C) after pressing enter, so when the calculation is done, it leaves cell empty
2. auto save the workbook when all the above operations are completed

Thank You
 
Upvote 0
Code:
Option Explicit




Private Sub Worksheet_Change(ByVal Target As Range)
    Dim thisrow As Long
    If Target.Column = 3 Then
        thisrow = Target.Row
        Range("B" & thisrow) = Range("B" & thisrow) - Range("C" & thisrow)
        Range("C" & thisrow).ClearContents
    End If
    ActiveWorkbook.Save
End Sub
 
Upvote 0
Code:
Option Explicit




Private Sub Worksheet_Change(ByVal Target As Range)
    Dim thisrow As Long
    If Target.Column = 3 Then
        thisrow = Target.Row
        Range("B" & thisrow) = Range("B" & thisrow) - Range("C" & thisrow)
        Range("C" & thisrow).ClearContents
    End If
    ActiveWorkbook.Save
End Sub

This is not working for me. Excel freezes after first input. Not sure why.
 
Upvote 0
What has changed in your workbook. It worked before we added the clearcontents line which should have no effect on the input
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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