The difference between numbers within same cell

[Vxuser]

Good day,

I am interestes in a formula or VBA code that can make the minus (-) between numbers in same cell.
In order to be more specific I will explain exactly what I want.

In cell A1 I will put a value (total no. of miles) at the begining, lets say 3000, than every day the no. of miles will decrease by a different values.
I want in the 1st day to type 2766 in cell A1 and to appear in cell B1 the diferrence 3000-2766= 234.

2nd day I will type 2501 in cell A1 and in B1 to appear the difference from the miles I did from day 1 to day 2 ... 2766-2501=265

3rd day I will type 2389 in cell A1 and in B1 to appear the difference from the miles i did from day 2 to day 3 .... 2501-2389=112

And so on.

At the moment I am using a VBA code In order to add the numbers but I need also one VBA code to substract the numbers. I will paste down the code for adding the numbers:

Private Sub Worksheet_Change (ByVal Target as Range)
If Not Application.Intersect(Range("A1"), Range(Target.Address)) Is Nothing Then
If Target.HasFormula = True Then Exit Sub
If Target.Value <> " " Then
Target.Offset(0,10).Value = Target.Offset(0,10).Value & " " & Target.Value
End If
End If
End Sub

And Module 1:

Function SumNums(pWorkRng As Range, Optional xDelim As String = " ") As Double
Dim arr As Variant
Dim xIndex As Long
arr = Split(pWorkRng, xDelim)
For xIndex = LBound(arr) To UBound(arr) Step 1
SumNums = SumNums + VBA.Val(arr(xIndex))
Next
End Function


Thank you for taking the time to read my post.

 

[NoSparks]

Going by your verbiage only, try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oldval As Double, newval As Double
If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$1" Then
    Application.EnableEvents = False
    newval = Target.Value
    Application.Undo
    oldval = Target.Value
    Target = newval
    Target.Offset(1) = oldval - newval
    Application.EnableEvents = True
End If
End Sub

 

[Vxuser]

Thank you for your interest of helping me.

I have tried what you suggested but nothing is happening, not even the values to copy themselfs in the offset.

 

[Vxuser]

Sorry for the previous reply.
I relooked on the code and I saw a mistake that I've made.

Your code is working.
Thank you so much!

 

[kweaver]

Placing a value in a cell and then trying to compute on it and replace it will create a circular reference.

Can't you use this?

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]7[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]2766[/TD]
[TD="align: right"]234[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2501[/TD]
[TD="align: right"]265[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2389[/TD]
[TD="align: right"]112[/TD]

</tbody>

Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]=A7-B7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


with C8 filled down: =B7-B8

 

[Vxuser]

I worked it out with the code mentioned above.
Thank you!

 

[DanteAmor]

Good day,

I am interestes in a formula or VBA code that can make the minus (-) between numbers in same cell.
In order to be more specific I will explain exactly what I want.

In cell A1 I will put a value (total no. of miles) at the begining, lets say 3000, than every day the no. of miles will decrease by a different values.
I want in the 1st day to type 2766 in cell A1 and to appear in cell B1 the diferrence 3000-2766= 234.

2nd day I will type 2501 in cell A1 and in B1 to appear the difference from the miles I did from day 1 to day 2 ... 2766-2501=265

3rd day I will type 2389 in cell A1 and in B1 to appear the difference from the miles i did from day 2 to day 3 .... 2501-2389=112

And so on.

At the moment I am using a VBA code In order to add the numbers but I need also one VBA code to substract the numbers. I will paste down the code for adding the numbers:

Private Sub Worksheet_Change (ByVal Target as Range)
If Not Application.Intersect(Range("A1"), Range(Target.Address)) Is Nothing Then
If Target.HasFormula = True Then Exit Sub
If Target.Value <> " " Then
Target.Offset(0,10).Value = Target.Offset(0,10).Value & " " & Target.Value
End If
End If
End Sub

And Module 1:

Function SumNums(pWorkRng As Range, Optional xDelim As String = " ") As Double
Dim arr As Variant
Dim xIndex As Long
arr = Split(pWorkRng, xDelim)
For xIndex = LBound(arr) To UBound(arr) Step 1
SumNums = SumNums + VBA.Val(arr(xIndex))
Next
End Function


Thank you for taking the time to read my post.

Try this, It's another way to do it

Dim old
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address(0, 0) <> "A1" Or Target.Count > 1 Then Exit Sub
    old = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "A1" Or Target.Count > 1 Then Exit Sub
    Target.Offset(1) = old - Target.Value
End Sub

 

[Vxuser]

Thank you!

 

[DanteAmor]

Thank you!

You're welcome. Thanks for the feedback.