Forum Discussion
Keep number from changing after setting cell back to zero
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
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!
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
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.
Jamil: Thank you so much. It works exactly as I wanted it to. It's very much appreciated
- You are welcome. Thanks for the feedback.
