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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Before your "For Each" statement, put this line.
Code:
On Error Resume Next
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Hi John and Richard!

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

Many thanks,

vcoder
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
vcoder

Did you take look at the link I posted?
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
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
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
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.
 

Forum statistics

Threads
1,181,659
Messages
5,931,273
Members
436,786
Latest member
Deniel

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
Top