1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with Run-time error -2147417848

Discussion in 'VBA Macros' started by Eloise T, Mar 13, 2019.

  1. Eloise T

    Eloise T Active Member

    Messages:
    867
    Every time I start to assemble a question here, I always hope to figure out the answer before I have to hit [Create Thread]. I'm still batting 0. :(

    I have become a fan of using Conditional Formats, but it never fails, when I add more data, Conditional Format thinks it needs to make an adjustment, and in reality, it does not ...which brings me to my predicament here:

    I have a macro (please see attachment) called, you may have guessed "Conditional_Format_Reset" which is supposed to delete the (3) old Conditional Formats and reset them back to what I need them to be.

    The problem I'm facing is figuring out why the macro successfully steps though the code the first loop and hangs the 2nd time through with the following error:

    upload_2019-3-13_12-55-23.png
    Thanks for you help in advance.

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    Runs fine on my machine, without error.

    My guess is that error is caused by your network environment (ex: unstable network drive connection). Or your terminal environment.
  3. Eloise T

    Eloise T Active Member

    Messages:
    867
    Chihiro, I tried it on another computer and you're correct, it worked; however, when I delete the 3 Conditional Formats and then run the macro, it doesn't restore the Conditional Formats. Got any ideas?
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    What do you mean? Is it via code or when it's deleted manually?

    I'm not really a fan of this sort of set up. Data entry/updates should be done on and/or kept in flat table. Not in cross tab structure.

    Data can then be presented as report in whatever structure desired.
  5. Eloise T

    Eloise T Active Member

    Messages:
    867
    When I manually delete the Conditional Formats and then run the macro (macro should recreate Conditional Formats), the CFs are not recreated.

    Also, I'm not clear on what you are referring to as flat table vs. cross tab structure?
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    Flat table would have... single column for each category.
    I.E. Name, Date, Event, Flag etc. You only need record for where x is marking each line.

    Cross tab is like pivot table, where one of column is transposed as column labels.

    This is critical step in creating model and common mistake made by people using Excel.
  7. Eloise T

    Eloise T Active Member

    Messages:
    867
    Column A is a list of names (redacted). Column B and C report the corresponding number of times by week that name appeared, based on the number of Xs in the remaining columns beginning with Column D (September 1, 2013). What would have been a better method to design this sheet so it was in the "flat table" design? Thank you in advance for your assistance.
    Last edited: Mar 16, 2019 at 5:48 PM

Share This Page