Mar 27 2018
09:46 AM
- last edited on
Jul 25 2018
11:29 AM
by
TechCommunityAP
Mar 27 2018
09:46 AM
- last edited on
Jul 25 2018
11:29 AM
by
TechCommunityAP
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?
Mar 27 2018 11:45 AM - edited Mar 27 2018 12:05 PM
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
Mar 27 2018 12:17 PM - edited Mar 27 2018 12:29 PM
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.
Mar 28 2018 10:13 AM - edited Mar 28 2018 10:13 AM
Hi,
Sorry for the late reply!
Mar 28 2018 11:18 AM - edited Mar 28 2018 11:20 AM
Solutionhopr37
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
Mar 29 2018 07:52 AM
Hi 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!
Mar 29 2018 09:03 AM - edited Mar 29 2018 09:04 AM
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
Apr 03 2018 10:22 AM
Thanks for the replies guys. Sorry, I've been away for the holiday weekend. I'll check this out as soon as I can. Much appreciated.
Apr 04 2018 05:37 AM
Jamil: Thank you so much. It works exactly as I wanted it to. It's very much appreciated
Apr 04 2018 05:48 AM
Apr 04 2018 06:49 AM - edited Apr 04 2018 06:55 AM
Jamil: one question.
I'm changing some values to see how everything works.
The only thing I can't figure out is what value2 means?
I've changed this to value1 and value3 and they both give me an error.
How does value2 figure into this? I'm "assuming" value2 is the next cell after the target cell?
Apr 04 2018 07:05 AM - edited Apr 04 2018 07:06 AM
Range/Cell/target . value2 Returns or sets the cell value.
Range/Cell .Text gives you a string representing what is displayed on the screen for the cell. Using .Text is usually a bad idea because you could get ####
Range/Cell .Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)
Range/Cell .Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.
Detailed Explanation from by my friend Charles https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/
the next adjacent cell value targeted using target.offset
Apr 04 2018 07:11 AM
Great. Thank You for the explanation
Apr 04 2018 07:13 AM
Apr 08 2018 09:56 AM
Apr 12 2018 05:05 AM - edited Apr 12 2018 05:28 AM
Hey Jamil, quick question hopefully you can help with on the last trouble you solved for me. ( This is by no means an emergency or that important that I can't work around it)
I've noticed if I copy the rows in column "C" ( I grab the first couple of numbers which for the moment are zero's and copy them all the way down column "C" by dragging them, I get an error message.
Run-time error 13:
type mismatch
If I look at the formula then this is highlighted in yellow:
"If Target > 0 Then"
Here is the formula in case you forgot:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C4", Range("C" & 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
Mar 28 2018 11:18 AM - edited Mar 28 2018 11:20 AM
Solutionhopr37
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