Forum Discussion
Keep number from changing after setting cell back to zero
3 colums
1st column is the starting total
2nd column is the amount subtracted from the 1st column
3rd column is the new total by subtracting the 1st and 2nd columns
Question:
After subtracting the amount from the second column and thus changing the amount in the 3rd column, how would I go about clearing out the second column without the third column's number changing until I add a new number into the second column?
Example:
1st column: 100
second column: 10 ( subtracting 10 from 100)
3rd column: 90 ( remaining balance)
Now. I would like to "zero out" the second column while keeping the 3rd column as is
1st column: 100
second column: 0
3rd column: 90 ( This number doesn't change when you use "zero" in the second column)
AND THEN be able to add a new number to the second column and have that number reflect a change in the 3rd column
1st column: 100
second column: 30
3rd column: 60
Zero out the 2nd column again yet keep the third column as 60 until I add a different number again to the 2nd column.
The way it woks now is if I put 10 ( as an example) in the second column, it changes the 3rd column to 90. BUT if I change the second column back to zero it changes the 3rd column back to 100. I want to keep it at 90 until I replace the zero with a different number.
Does that make sense?
hopr37
This can be done with few lines of VBA code which i wrote below. please see the attached workbook.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B2", Range("B" & Rows.Count).End(xlUp))) Is Nothing Then Application.EnableEvents = False If Target > 0 Then Target.Offset(, 1).Value2 = Target.Offset(, -1).Value2 - Target.Value2 End If End If Application.EnableEvents = True End Sub
16 Replies
hopr37
This can be done with few lines of VBA code which i wrote below. please see the attached workbook.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B2", Range("B" & Rows.Count).End(xlUp))) Is Nothing Then Application.EnableEvents = False If Target > 0 Then Target.Offset(, 1).Value2 = Target.Offset(, -1).Value2 - Target.Value2 End If End If Application.EnableEvents = True End SubHi Jamil,
I don't think that this solution is what he wants!
I understood from his comment that he wanted the subtraction to be cumulative and to remember all the values he entered into the cell!
If he entered 10 into cell B2 and deleted it or replaced it with 0, then returned another day and entered a new value in the cell, he wants to subtract the sum of both the new value and the old value he had deleted before!You need to add some code that stores all the values that he has entered into the cell somewhere and records it, then retrieves them and subtracts from the total!
That's why I called the solution that needs an unusual solution!
Such a solution needs time and effort!
Hi Haytham,
Thanks for pointing that. If OP really wants to do that way then, OP can use this code below.Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B2", Range("B" & Rows.Count).End(xlUp))) Is Nothing Then Application.EnableEvents = False If Target > 0 Then Target.Offset(, 1).Value2 = Target.Offset(, 1).Value2 - Target.Value2 End If End If Application.EnableEvents = True End Sub
Hello,
Use the manual calculation mode so that any change won't reflect until you press F9.
To turn this mode on go to Formulas (Ribbon tab) >> Calculation >> Calculation Options >> Manual.
Also, you can clear the below check box to keep the value as it is after saving and closing the workbook.
Hope that helps
Haytham
Thank you so much. However. It doesn't seem to work quit right.
If I start with 100 and subtract 10 and hit f9 the total does change.
I can then change the amount back from 10 to 0 and save it. The amount stays at 90-good.
If I THEN change the 0 back to 10, hit f9 then it SHOULD go to 80 but it doesn't. It stays at 90
If I put in 20 THEN it goes to 80.
I need the number to continually go down.Hi,
Sorry for the late reply!
anyone?
