VBA code not working anymore? Please help!

Question

Hello everybody,

I am currently managing a stock tracking file, which is edited manually to a large extent.

Currently, the four significant columns look like this:

Here, column I is the actual quantity in stock. If my code was functioning, which it was as of yesterday (and I have made no edits whatsoever to it), any number entered into column J should subtract that number from the quantity (so I4 should be 550 - 50 = 50 in the case above for example) after which the number also disappears from column J. The same applies for column K, where it just adds quantity rather than subtracts.

Finally, column L should track any point in time where the quantity changes in value (so we can see if any of the quantities have not been touched for longer periods of time).

This all worked as of yesterday, but as of this morning (central European time) none of these codes work anymore, neither the subtraction, addition or date & time columns.

My VBA code for this entire flow looks like this:

Can anybody assist me in figuring out what the problem is and how to potentially fix it so it all works again? It would be truly amazing and would really make my day!

Thank you so much everybody :)

Best regards,

David

Answer 1

Hi David,

It seems to be working fine on my end. Should there be value left on columns J and K after I have typed a number? Or does it supposed to disappear?

This is the code I am using:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range, c As Range

Set r = Intersect(Target, Columns("B:C"))

If Not r Is Nothing Then

Application.EnableEvents = False

For Each c In r

    With Cells(c.Row, "A")

        If IsNumeric(.Value) Then

            .Value = .Value + IIf(c.Column = 2, -c.Value, c.Value)

            Cells(c.Row, "D").Value = Now

            Cells(c.Row, "D").NumberFormat = "dd-mm-yyyy, hh:mm:ss"

            c.ClearContents

        End If

    End With

Next c

Application.EnableEvents = True

End If

End Sub

Awaiting for your response.

Regards,

John