Subtract cell from another and replace it with the difference

[Rafalski]

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

 

[alansidman]

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.

 

[Rafalski]

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).

 

[alansidman]

Put this code in the Worksheet_Change Event and not in a module

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.

 

[Rafalski]

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

 

[alansidman]

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

 

[Rafalski]

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.

 

[alansidman]

What has changed in your workbook. It worked before we added the clearcontents line which should have no effect on the input