Help with Run-time error -2147417848

[Eloise T]

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:


Thanks for you help in advance.

 

[Chihiro]

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.

 

[Eloise T]

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?

 

[Chihiro]

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.

 

[Eloise T]

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?

 

[Chihiro]

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.

 

[Eloise T]

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.