Unable to get the subtotal property of the worksheet function class

I'm receiving an error while executing my code.The reason i understand is when the filter of invoices submitted valued above 100k is done, lets say in this case we don't have any invoices submitted, in the system i've set it as "not delivered to customer". So, in this case how can the system take out the average days from the 2 invoices , above 100k and where both are not submitted. Screenshot of the code is below;

Please Login or Register  to view this content.

The error i'm receiving while executing this is "Unable to get the subtotal property of the worksheet function class". I do understand the reason for the error. Its just that, in column "d" of the main sheet (recognized in vba code as "ws_main"). In the main sheet, there's a column D. What is fed in that column is , if the delivery is not made from logistics to finance dept and obviously, unless if the delivery from logistics is not received, we cannot deliver it to customer. So, ideally if both dates are empty, then, in column D, I have kept it as, if empty, then it should write as "not delivered to customer". But our program is basicallly to take into account of the average days it takes from Delivery from Logistics to Finance (DLF) and then average days it takes from FINANCE to customer (DFC)
But if there;'s a case of "'not delivered to customer for all invoice, how can the system get an average, which is why the error is happening. Please help

Administrative Note:
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

As I said, I did it for you this time.

What do you want the average of no data to be?

You can do two things. First declare your variables as Double (most of them are Variant at the moment), then uncomment the On Error Resume Next line and add On Error Goto O after the line that assigns the NDI value.

You can use logic like this and replace the 0 with whatever you want to show in each instance:

Please Login or Register  to view this content.