Hi,
I hope someone can help me but I have the below code that calculates overdue days by using the NETWORKDAYS formula.
It works but the problem is that there are times I can have over 15000 rows and it takes a complete age.
Please Login or Register to view this content.
I've tried
and also a loop but I am not having much success with speeding it up.Please Login or Register to view this content.
Is there an alternative way of getting the above macro to run faster?
Help
Last edited by Ratso; 04-25-2019 at 08:57 AM.
Why are you using FormulaArray? This does not appear to be an array formula, and that could be part of the reason for the long time. You might try Formula instead.
Why are you doing this in VBA instead of putting the formula directly in the worksheet?
If my suggestion does not help I suggest you attach your file with sample data, although it appears that it has private data that you would have to blank out.
By the way, using 'BANK HOLIDAYS'!C1 probably will not do what you want. That argument has to be a range that has a list of dates for holidays, and you have provided just a single cell.
Last edited by 6StringJazzer; 04-25-2019 at 11:25 AM. Reason: Grayed out incorrect comment, see later post for correction
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Hi 6StringJazzer,
Many thanks for the reply.
Ok I have changed the Array to Formula. Still no luck.
I have attached a sample worksheet with the DaysOverdue Macro which is currently running over 4 minutes.
This is a heavy VBA workbook which requires the end user to push a button and the VBA does the rest and spits out the results in a separate worksheet for them to work. So manually adding the formula to the worksheet isn't an option.
As this would appear to be business related why use 'Working Days'.
Surely settlement of debt is purely date related.
i.e. settlement due in 30 days or 60 days etc., not dependent on 'holiday' variables.
Make life easier for yourself and your cash-flow.
torachan.
Hi Torachan,
This is a unbilled report that is sent to different sites because of issues. The days overdue column tells the site the working days age of the account when it exceeds over so many working days then the account is written off and they get told off
Odd I know but in my business and these types of accounts we work in actual working days hence the BANK HOLIDAYS tab.
I forgot that you were using R1C1 notation here so this comment is incorrect. However, you are using the entire column. That is part of the problem.Originally Posted by 6StringJazzer
I created a named range for your holiday list, and updated the code. This now runs in a couple of seconds.
Hi 6StringJazzer,
Works perfectly. Many thanks
You are very welcome, and thanks for the rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks