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

It just crashes - no errors, just shuts down.

I tried to recreate my excel sheet into a new sheet now with ONLY one this one macro and it still crashes. So for some reason my macro has stopped functioning now (despite working as of yesterday for about a week in a row).

How do I fix this? It highlights the following:

Answer 2

I did, and I can't find a solution in that link either :( I think it's a problem with my macro, not excel or the size of it, because I tried copy pasting ONLY this macro into a new excel sheet and it gives me the following errors:

It seems that I need to fix this macro somehow, not sure how tho? :(

Answer 3

Hi David,

Please remove the chunk of codes as shown on the screenshot below and see if it fixes the issue. I thought you have already removed those.

Please let me know what will happen.

Regards,

John

Answer 4

Remove all of these codes.

'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

You do not need those since you have already declared the automatic date above.

Regards,

John

Answer 5

Hello John,

I already did this and it did not work, which is why I put it back :(