Exit Sub alternative

[Bishop]

I have the following code:

Option Explicit

Sub Consolidate()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim Fnum As Long
Dim mybook As Workbook
Dim CalcMode As Long
Dim sh As Worksheet
Dim ErrorYes As Boolean
Dim DCLastRow As Integer 'DirectorCopy
Dim MCLastRow As Integer 'Monthly Compiler

Dim center(18) As String
center(1) = "Bardstown"
center(2) = "Bothell"
center(3) = "VCollinsville"
center(4) = "El Paso"
center(5) = "Evansville"
center(6) = "Greensboro"
center(7) = "VHeathrow"
center(8) = "Joplin"
center(9) = "Kennesaw"
center(10) = "Lafayette"
center(11) = "Malvern"
center(12) = "VManhattan"
center(13) = "VMansfield"
center(14) = "VOttawa"
center(15) = "VPonco City"
center(16) = "VReno"
center(17) = "VSioux City"
center(18) = "VTerra Haute"

Dim FileCount As Long
Dim ScoringAve As Double
Dim i As Long


'Fill in the path\folder where the files are
MyPath = "X:\C&A Analysts Team\PF Process\1 Tally & PF's Work in
Progress\Centers\"

For i = 1 To 18

' 'Add a slash at the end if the user forget it
' If Right(MyPath, 1) <> "\" Then
' MyPath = MyPath & "\"
' End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & center(i) & "*.xl*")

If FilesInPath = "" Then
MsgBox "No files found in " & center(i)
Exit Sub
End If

From here there's a whole lot of things that are to take place if there ARE
files in the folder. But if the folder is empty then I get the msgbox and
the sub stops.

This last block is not really what I want to do. If there are no files in
that particular folder then I want stop at this point, have my For loop
increment and continue on to the next folder. How do I make this happen?

 

[OssieMac]

Hi Bishop,

Do you want to break out of the for i = 1 to 18 loop or do you want the loop
the continue to 18.

If you want to break out of the loop then replace Exit Sub with i = 18

If you want to continue with the loop the just remove Exit Sub.

 

[JoeU2004]

This last block is not really what I want to do. If there are no files in
that particular folder then I want stop at this point, have my For loop
increment and continue on to the next folder. How do I make this happen?

Well, you can always use GoTo, to wit:

For i = 1 To 18
...some code...
If FilesInPath = "" Then
MsgBox "No files found in " & center(i)
GoTo ContinueLoop
End If
...some more code...
ContinueLoop:
Next i

If you do not mind additional indentation (good practive, albeit optional),
you can avoid the GoTo in this case by some restructuring, to wit:

For i = 1 To 18
...some code...
If FilesInPath = "" Then
MsgBox "No files found in " & center(i)
Else
...some more code...
End If
Next i

Although some people get religious about this, there are reasonable
arguments for both forms. Even Dijkstra came to realize this some time
after publishing his now-infamous "Goto is Dangerous" manifesto.


----- original message -----