Share via

VBA code not working anymore? Please help!

Anonymous
2020-08-13T07:11:10+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

16 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-08-13T08:34:53+00:00

    Hi David,

    Macro could be the reason why your Excel keeps on crashing. That is why as much as possible I use Excel formula only on my reports. Try performing an online repair to your Office to fix the issue.

    1. Open Control Panel > Programs and Features
    2. Click Microsoft Office in the list of installed applications
    3. At the top, click "Change" > On the resulting dialog, click "Repair"
    4. Choose the online repair to make sure everything gets fixed

    Please let me know what will happen.

    Regards,

    John

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2020-08-13T08:27:54+00:00

    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?

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2020-08-13T07:26:29+00:00

    Please remove these codes

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2020-08-13T07:28:56+00:00

    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

    Was this answer helpful?

    0 comments
  5. Anonymous
    2020-08-13T07:18:17+00:00

    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

    Was this answer helpful?

    0 comments