For Each .... In Selection --- skip to next if not found

[vbacoder]

Hi everyone,

I am using a statement to step through a list of filenames in a list using the following syntax:

For Each filename In selection

.....

Next

Occasionally, the filenames I add to the selection range do not appear in the source folder. When this happens, the macro throws up an error message and stops. If no match is found, I want it to automatically skip to the next filename in the list. I know there is a way to do this, I just do not know the syntax for achieving this.

This is how I think part of it is done, using the .Find statement:

For Each filename In selection

Workbooks.Find filename: = "...blah blah .."

Next

If no match is found, the statement is False, and I then need to add another statement to tell the code to skip to the next in the list if the filename does not appear anywhere in the selection range/list.

I would be very grateful for any suggestions on this.

Many thanks,

vcoder

 

[Norie]

vcoder

That code just seems overcomplicated and is a fine example of spaghetti code.

Why do you have 2 seperate routines just to put a string into a cell?

PS Did you evere try my Dir idea?

 

[vbacoder]

Hi Norie,

The important bit I am trying to demonstrate is that if the file is not present in the folder, the routine does not go ahead and call the routines. If the file is found, it does. This did not happen before.

I included two 'silly' calls to insert text purely as an example... to test the procedure.

vcoder

 

[Norie]

Well why not use Dir to see if the file exists, that's what it does.

And if this is just 'silly' code it might not be a good idea to use On Error, who knows what other errors it might hide?

 

[vbacoder]

I did try the dir example, but I want to suppress errors as I don't want the subroutine to be interrupted with error message boxes.

My code may be viewed as spaghetti-like, but this is the only way I could achive my goal. I welcome any improvements, provided they do not detract from the objective.

vcoder

 

[vbacoder]

And if this is just 'silly' code it might not be a good idea to use On Error, who knows what other errors it might hide?

I mean, my inclusion to include calls to routines adding text are only for demonstration in this eg. .....

I aggree with your point on the use of On error. This is a disadvantage I just have to deal with. Provided the files are consistent, there should not be a major issue.