Macro crashes intermittently - believe on the listrows.add function

  • [INDENT]Hi,


    I've been working on this issue since yesterday when my macro suddenly started failing. Here's what happened and what I did:


    Excel 2013


    1. The macro has a userform with a number of pages to add or delete items from a set of named ranges based on tables.
    2. On Friday, it was working fine
    3. On Monday, I finally figured out after stepping through it and numbering each of the lines, that I was first having a error on the sort.clear (something with the object and a 424 error), so I moved the clear to another part of the with statement for the sort and still received the error.
    4. I took out the clear, ran it again and still received the error and decided to do the sort with an array.
    5. After a while, I figured that out and then started getting a disconnect error on the listrows.add (which is before the sort). That started to work when I added "(position:=1)"
    6. Anyway, I finally got it to work last night.


    This morning, I ran it and it was fine. Then I used the delete day function and add function which appeared to work correctly. I then tried doing the following:


    1. Delete a day -> Clear Form
    2. Add in a day that still existed (correct error message came up) -> cleared form
    3. Add in the deleted day (system spun around for a while, closed and reopened excel) - no error message
    4. Redid using the same steps, same problem
    5. Tried again, stepping through the code and it appears to be on the listrows.add code or the line after.


    Added a step between 3 and 4 to close the form after the initial add and error message and then retried step 3 above - worked fine, although I'm not sure I trust it.


    BTW, should mention that I'm not a developer but am a functional, manual tester and have done some small macros before.


    Any thoughts, help? I'm attaching the .xlsm file and the two procedures that I've been working with are the AddDelivDay and DelDelivDay. The Add has a lot of commented out code from my attempts to make it work yesterday. I'm beginning to think it is something with the userform but no real reason why.


    Thanks for any and all help.[/INDENT]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!