Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exit Sub alternative
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exit Sub alternative
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. -- Regards, OssieMac "Bishop" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exit Sub alternative
"Bishop" wrote:
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 ----- "Bishop" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternative to IF | Excel Worksheet Functions | |||
get the Alternative text of a pic. | Excel Programming | |||
Exit Field vs Exit Button...... | Excel Programming | |||
If alternative | Excel Worksheet Functions | |||
If a called sub exit, how to the caller exit right away? | Excel Programming |