+ and - (plus and minus) in Excel list

[David77]

Hello everybody,

I am currently currently managing a stock overview in Excel (manually), where I have a set quantity. I want to create two columns next to my quantity column where I can enter numbers that should be substracted and added to the quantity, so it looks like this:

Now obviously I can just set the quantity number as (for I1 for example): =50-J1+K1, which would give me my current quantity. However, for future endeavours when the stock continues to decrease or increase, I would have to enter new numbers in J1 and K1 and thus my formula would break down as it would forget about the prior entries.

Is there any fancy way in which I can fix this? So I can simply just type in "5" in J1, whereafter it subtracts 5 from I1 = 45 and the number then disappears from J1.

I would greatly appreciate any assistance I can get in this matter! Thank you very much for your time everybody

Best regards,
David

 

[FormR]

Hi, you could try this code.

To use right, click the sheet tab where you want this to happen and choose "view code" and paste the below code into the window that opens up and then save your workbook as a macro enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Intersect(Target, Columns("J:K"))
If Not r Is Nothing Then
    Application.EnableEvents = False
    For Each c In r
        With Cells(c.Row, "I")
            If IsNumeric(.Value) Then
                .Value = .Value + IIf(c.Column = 10, -c.Value, c.Value)
                c.ClearContents
            End If
        End With
    Next c
    Application.EnableEvents = True
End If
End Sub

 

[David77]

Hi, you could try this code.

To use right, click the sheet tab where you want this to happen and choose "view code" and paste the below code into the window that opens up and then save your workbook as a macro enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Intersect(Target, Columns("J:K"))
If Not r Is Nothing Then
    Application.EnableEvents = False
    For Each c In r
        With Cells(c.Row, "I")
            If IsNumeric(.Value) Then
                .Value = .Value + IIf(c.Column = 10, -c.Value, c.Value)
                c.ClearContents
            End If
        End With
    Next c
    Application.EnableEvents = True
End If
End Sub

It works!!

Amazing! Thank you so much!!!

Please have a wonderful day, you've ensured that I will now

Best regards,
David