Hi all
My report spreadsheet has developed a problem & now has trouble reading some dates, causing a #N/A error. The formula had worked as expected up to this point. Now that I am looking at a date range which includes October it is behaving unusually. Put simply, it worked last week, but not this week.
Specifically, I am trying to index/match a named range which contains concatenated dates & text description, located in another workbook. The date ranges the data refers to is set by the user with a toggle button. The date/text is generated by a formula aligned with the toggle button. The named ranges are formatted appropriately, i.e. dates as Date. The macro references these dates from the master data and if they don't match of course it won't work, however this is not the issue.
The whole process is way too complex for me to explain in detail in just a few sentences. Essentially it looks at 'what we want' compared to 'what we have'. To do this it imports data, formats, sorts, creates a new workbook & worksheets, distributes, Index/Matches, performs calculations & applies Conditional Formatting according to varying conditions.
I’m hopeful someone is able to look at my file and help me work this problem out. It has taken months to get to this point and is frustrating to have something that seemed to be working well fall over.
I have attached a modified copy of the workbook with the relevant data included, so no importing required. The report will look to save a new workbook to a folder/filepath as contained in cell H2. Click the Run Report button and away it goes. It is throwing a 1004 error while trying to Sum a column where the failure has occurred – Sheet “JW Cooper”, line 37, for this data set. The code for this section is in Module ‘FormatJWCooper’, Sub IM_JWC2.
I have also included a ‘site map’ to navigate thru the various routines, if required. There is another data set on “Sheet 26” which refers to June/July and interestingly now throws the same error
Any help is very much appreciated.
Thanks & regards
Mark
My report spreadsheet has developed a problem & now has trouble reading some dates, causing a #N/A error. The formula had worked as expected up to this point. Now that I am looking at a date range which includes October it is behaving unusually. Put simply, it worked last week, but not this week.
Specifically, I am trying to index/match a named range which contains concatenated dates & text description, located in another workbook. The date ranges the data refers to is set by the user with a toggle button. The date/text is generated by a formula aligned with the toggle button. The named ranges are formatted appropriately, i.e. dates as Date. The macro references these dates from the master data and if they don't match of course it won't work, however this is not the issue.
The whole process is way too complex for me to explain in detail in just a few sentences. Essentially it looks at 'what we want' compared to 'what we have'. To do this it imports data, formats, sorts, creates a new workbook & worksheets, distributes, Index/Matches, performs calculations & applies Conditional Formatting according to varying conditions.
I’m hopeful someone is able to look at my file and help me work this problem out. It has taken months to get to this point and is frustrating to have something that seemed to be working well fall over.
I have attached a modified copy of the workbook with the relevant data included, so no importing required. The report will look to save a new workbook to a folder/filepath as contained in cell H2. Click the Run Report button and away it goes. It is throwing a 1004 error while trying to Sum a column where the failure has occurred – Sheet “JW Cooper”, line 37, for this data set. The code for this section is in Module ‘FormatJWCooper’, Sub IM_JWC2.
I have also included a ‘site map’ to navigate thru the various routines, if required. There is another data set on “Sheet 26” which refers to June/July and interestingly now throws the same error
Any help is very much appreciated.
Thanks & regards
Mark
