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,

I am John, an Independent Advisor and a Microsoft user like you. I'm here to work with you on this issue.

Please cut/paste the chunk of codes as shown in screenshot below into Notepad.

I hope it worked out well for you. If not, please reply back to this forum to let me know. I will be glad to follow up and help you.

Regards,

John

Answer 2

Please remove these codes

Answer 3

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)

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

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

c.ClearContents

End If

End With

Next c

Application.EnableEvents = True

End If

'Update 20140722

Dim WorkRng As Range

Dim Rng As Range

Dim xOffsetColumn As Integer

Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)

xOffsetColumn = 3

If Not WorkRng Is Nothing Then

Application.EnableEvents = False

For Each Rng In WorkRng

If Not VBA.IsEmpty(Rng.Value) Then

Rng.Offset(0, xOffsetColumn).Value = Now

Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"

Else

Rng.Offset(0, xOffsetColumn).ClearContents

End If

Next

Application.EnableEvents = True

End If

End Sub

Answer 4

Hi David,

Please see my screenshot above. : )

Thank you,
John

Answer 5

Hello John,

I tried doing that, but it didnt seem to work and now (this also happened before btw) my excel has been crashing non-stop whenever i try to make the slightest of edits

is my macro too big or something weird?