Replies

Legare,
Thank you so much for taking the time to SOLVE my problem. So often the problems or questions posted here must require fairly simple answers, so it takes someone special to look beyond the obvious. I was sure my problem had to be more than coding because of the other symptoms. Initially, VBE was not acting normally. As I gradually commented-out bits of code to zero-in on the cause, other symptoms appeared. After a code break, when I clicked END instead of DEBUG, Excel would hang and I’d have to use Task Manager to kill it. I was about to use a time delay to see if that would help.
I am writing this before I have taken a good look at your code because I wanted to let you know how much I appreciate your effort. But it appears you have written code to replace the advanced filter method.
When you say “It is most likely a memory leak in the Advanced Filter method”, are you suggesting we avoid the advance filter in a loop? Are you aware of a KB article about this? Is there any way I can help get the word out about this?

Legare,
Thank you so much for taking the time to SOLVE my problem. So often the problems or questions posted here must require fairly simple answers, so it takes someone special to look beyond the obvious. I was sure my problem had to be more than coding because of the other symptoms. Initially, VBE was not acting normally. As I gradually commented-out bits of code to zero-in on the cause, other symptoms appeared. After a code break, when I clicked END instead of DEBUG, Excel would hang and I’d have to use Task Manager to kill it. I was about to use a time delay to see if that would help.
I am writing this before I have taken a good look at your code because I wanted to let you know how much I appreciate your effort. But it appears you have written code to replace the advanced filter method.
When you say “It is most likely a memory leak in the Advanced Filter method”, are you suggesting we avoid the advance filter in a loop? Are you aware of a KB article about this? Is there any way I can help get the word out about this?

Steve,
The macro breaks at the line, “Range(“Range(“Advances”).AdvancedFilter Action:=xlFilterCopy, _” etc.
The value of “Firm” is different each time through the loop – see “Firm” column in Advances column G.
I have removed te code that requires network drive mappings so that it now looks for and creates folders on the C drive.
I have assigned values to the date variables.
It still breaks at a different spot each time. I wonder if the code is somehow out-running Excel’s refresh rate.
Modified code attached.

Steve,
The macro breaks at the line, “Range(“Range(“Advances”).AdvancedFilter Action:=xlFilterCopy, _” etc.
The value of “Firm” is different each time through the loop – see “Firm” column in Advances column G.
I have removed te code that requires network drive mappings so that it now looks for and creates folders on the C drive.
I have assigned values to the date variables.
It still breaks at a different spot each time. I wonder if the code is somehow out-running Excel’s refresh rate.
Modified code attached.

Steve,
The macro is “BrokerFeesPartTwo” and it’s breaking at line 158 with “AdvancedFilter method of Range class failed”
Thanks

Steve,
The macro is “BrokerFeesPartTwo” and it’s breaking at line 158 with “AdvancedFilter method of Range class failed”
Thanks

Hans,
Following your post about my error-trapping I reviewed and revised my process and moved the folder checking out of the loop. Now that other errors are no longer ignored, a new problem has been revealed! On the 20th record, the advanced filter fails. Have I overloaded a stack somewhere or something? Do you have the time or patience to look at my revised code?

Hans,
Following your post about my error-trapping I reviewed and revised my process and moved the folder checking out of the loop. Now that other errors are no longer ignored, a new problem has been revealed! On the 20th record, the advanced filter fails. Have I overloaded a stack somewhere or something? Do you have the time or patience to look at my revised code?

Hans,
Your error-handling advice is well-taken. I will try that.

Hans,
Your error-handling advice is well-taken. I will try that.

Steve,
I don’t think I can be specific. The original post describes the problem. There is no particular point in the code at which the problem occurs , but it’s during the “For Each cell In FirmRange” loop that the code just seems to cease functioning.

Steve,
I don’t think I can be specific. The original post describes the problem. There is no particular point in the code at which the problem occurs , but it’s during the “For Each cell In FirmRange” loop that the code just seems to cease functioning.

BrokerFeesPartTwo is the problem.
In some cases it is reassuring to the user to see the macro do its thing. I use the ScreenUpdating on longer macros and after the user is familiar with it.

BrokerFeesPartTwo is the problem.
In some cases it is reassuring to the user to see the macro do its thing. I use the ScreenUpdating on longer macros and after the user is familiar with it.

Hans,
Thank you for your interest. The original file has a great deal of information I cannot share, so I have made changes that will hopefully not mask the problem.