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

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Hi everyone,

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

Code:
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:

Code:
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Before your "For Each" statement, put this line.
Code:
On Error Resume Next
 
Upvote 0
Hi

You can use :

Code:
For Each fname In selection
On Error Resume Next
Workbooks.Open fname
If Err<>0 Then Goto NextFname
On Error Goto 0

NextFname:
   Next
 
Upvote 0
Hi John and Richard!

Thank you very much for your codes and suggestions. I will try both of these.

Many thanks,

vcoder
 
Upvote 0
Hi Guys,

I tried the following:

Code:
For Each filename In Selection
On Error Resume Next
Workbooks.open_...
If Error <> 0 Then Goto NextFname
On Error Goto Continue 

Continue: Call sub_1
               Call sub_2

NextFname: 
   Next

It skips beautifully when the filename is not found in the folder, but when it is found, the file is opened, and the code doesnot proceed to the next line. Instead, it restarts and goes to the next filename in the list. (perhaps the If Error <> 0 part is too broad, so maybe if I equate to an error code corresponding to 'file not found', this may solve the problem)

If filename is present in the folder, I want to open it and 'continue' with the remaining lines in the code - the call statements to other subs in the module. I've tried many variations of the code above, to try to solve the problem, but to no avail.

When I tried John's code - using
Code:
On Error Resume Next
before my original
Code:
For Each filename In Selection
line, the code skipped erroneous filenames, but because I had my Call sub statements in the FOR loop, it executed these on old files, when an existing filename was not found. So really, on error I want to skip to the next filename without calling the subs, and on error = 0, I want to continue to the call statements.

I would be very grateful for any advice.

Many thanks,

vcoder
 
Upvote 0
I would suggest using Dir rather than hiding the error using On Error Resume Next.
Code:
If Dir(fname)="" Then
     Msgbox fname & " not found."
Else
     ' code if file exists
End If
I would also suggest not using Goto, not using Continue and not using Call.


Spaghetti code
 
Upvote 0
Hi Norie,

Thanks for your suggestions. I would prefer to suppress any error messages during execution as I don't want the macro to stop everytime an error is encountered.

Perhaps this might work:

Code:
For Each filename In Selection
	If Dir(fname)="" Then Goto NextFname 
Else
	Call sub_1
	Call sub_2

NextFname: 
   Next

Also, please could you explain why you wouldn't use Goto and Call statements. Do these slow down program execution?

Many thanks,

vcoder
 
Upvote 0
vcoder

Did you take look at the link I posted?
 
Upvote 0
Sorry, didn't click on it before replying. I also edited my post whilst you were replying, to add some code that might work. What do you think of this? I am not too concerned with the inefficiency of the code at this point, as I have other, more significant bottlenecks in the program.

Many thanks for your help.

vcoder
 
Upvote 0
Well, after some trial and error I implemented the following, which now does what I want it to do:

Code:
sub file_ops()
On Error GoTo nextfname:
For Each target In Selection
    id = target.Value
    rownumber = rownumber + 1
        Workbooks.Open Filename:="C:\" & id
        Windows("main_excel.xls").Activate
            Call routine_1(rownumber)
            Call routine_2(rownumber)
nextfname: Resume skipcalls:
skipcalls:
Next
End Sub


Sub routine_1(rownumber)

Range("b" & rownumber) = "success"

End Sub

Sub routine_2(rownumber)

Range("c" & rownumber) = "at last!!!"

End Sub

The section:
Code:
nextfname: Resume skipcalls: 
skipcalls: 
Next
looks silly, but it was the only way I could get the procedure to work as I wanted - on error, go to next file in list and don't call routines... on success call routines.
 
Upvote 0

Forum statistics

Threads
1,219,006
Messages
6,145,714
Members
450,635
Latest member
Rookie3510

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top