How can I avoid changing a loop counter within a loop?

hi all,

I've worked on a solution for this thread (http://www.excelforum.com/excel-prog...-automate.html) but have been mentally challenged with how to avoid changing the loop counter in one of the loops I have used to resort an array of file names from the getopenfile dialog.

The aim of the shown code (see post 12 of the above link for attached file) is to check if the file containing the macro is included in the array returned by getopenfile while sorting the array of file names, and if so, moving it to the end of the array for "deletion" by redimming the array to exclude the last item. This problem of the open file being selected in the dialog may never arise, but... as the OP's request in the other thread was to allow two-way comparisons between numerous files, I've considered it likely enough to test for.

Here's the code I have settled for esp between the commented lines of hash symbols, which does change the counter (see the commented exclamation marks), but prevents an infinite loop (on my second try!) by using a second boolean flag of "HasCounterBeenChanged".

Is there a better way of doing this?
Or, alternatively (not in my thread title), is it possible to prevent the active file being selected through one of the arguments in the getopenfilename method?

Please Login or Register  to view this content.

Thanks in advance for any suggestions,
Rob

Why not just remove the file from the array before you sort it?

Please Login or Register  to view this content.

Is there any other reason to sort the filenames?

If not, you can just set the entry to a null string, and test later when you do whatever you plan to do (untested):

Please Login or Register  to view this content.

hi guys,

Thankyou for your suggestions - I needed other perspectives & to take the time away from it to remove my "tunnel vision".

I've decided to go with the following modification of Andy's suggestion:

Please Login or Register  to view this content.

I maybe wrong (& I haven't tested it) but I think this quicker exit may be more efficient because it stops looping as soon as the "masterfile" is noted in the array. Although I recognise that any milliseconds which may be saved are totally insignificant in the likely time taken by this part of the small procedure - but anyway...

Andy,
Thanks for subtly pointing out that I didn't need to make extra function calls to extract the filename when I can use the ".Fullname" property.

Shg,
- Thanks, I've absorbed your use of "Not IsArray(...)" because I think it is more understandable & closer to self-documenting than my previous "typename(...) = "boolean"".
- I've chewed over your question of "why sort the filenames...?" & without feedback from the OP, my thoughts are:
1) This was more important when I started developing a solution because I didn't initially perform a search/match against the header row (I do now within the newer code).
2) Sorting the filenames allows for the (non-standard/duplicate) links to be inserted in an orderly fashion on the spreadsheet rather than being entered "as is" (in no discernable order) from the getopenfilename array.
3) Although only 2 loops (inserting links & possibly closing files) are completed in the code at this stage, I can imagine the OP asking for more functionality. If more functionality resulted in another loop, I could end up with more testing within the loops (or even per specific action) than having just one, which I get by moving the "is master file included" test/loop to the start.
- That's a nice use of the Match function , it took me a while to understand that the "avfile" argument, is the array, allowing the "i" to be the returned match & therefore didn't need to be stated as "avfile(i)".


Thanks
Rob