Mysterious Case Select error message. Help find cause please. Thx

ChetShannon

Board Regular
Joined
Jul 27, 2007
Messages
133
Office Version
  1. 365
Platform
  1. Windows
Hi,
Am getting an error on my Case Select statement. The error message is - "Case without Select Case". And I could not figure out where the error was this Case Select which has about 7 different possible "Cases" and so I removed each Case one by one to see which one was causing the error message and what I have below is the one causing the error message because the code seems to run fine without this one section Not sure what's wrong with it . Any help would be appreciated.

Section causing the error-
Code:
Case "Time Changed, Equipment Type"
            If Cells(RowY, 13) = HomeLoc Then
            
                'departure case
                If DeptTm1 = DeptTm2 And Equip1 = Equip2 Then OutputText = ""
                
                If DeptTm1 = DeptTm2 Then
                'equip only changed
                    OutputText = "Flt" & FltNbr2 & " " & Dow1 & " changed from " & Equip1 & " to " & Equip2
    
                'equip only because time change < minimum time to show-
                ElseIf DeptTm1 > DeptTm2 And 24 * 60 * (DeptTm1 - DeptTm2) < MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Changed from " & Equip1 & " to " & Equip2
                    
                'equip only because time change < minimum time to show-
                ElseIf DeptTm2 > DeptTm1 And 24 * 60 * (DeptTm2 - DeptTm1) < MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Changed from " & Equip1 & " to " & Equip2
                    
                'time changed & equip changed
                ElseIf DeptTm1 > DeptTm2 And 24 * 60 * (DeptTm1 - DeptTm2) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Dep time change from " & Format(DeptTm1, "hhmm") & _
                        " to " & Format(DeptTm2, "hhmm") & " changed from " & Equip1 & " to " & Equip2
                    
                'time changed & equip changed
                ElseIf DeptTm2 > DeptTm1 And 24 * 60 * (DeptTm2 - DeptTm1) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Dep time change from " & Format(DeptTm1, "hhmm") & _
                        " to " & Format(DeptTm2, "hhmm") & " changed from " & Equip1 & " to " & Equip2
                'End If
                
                If DeptTm1 > DeptTm2 Then Cells(RowY, 26) = -24 * 60 * (DeptTm1 - DeptTm2) Else Cells(RowY, 26) = 24 * 60 * (DeptTm2 - DeptTm1)

            Else
            
                'arrival case
                If ArvlTm1 = ArvlTm2 And Equip1 = Equip2 Then OutputText = ""
                
                
                If ArvlTm1 = ArvlTm2 Then
                'equip only changed
                    OutputText = "Flt" & FltNbr2 & " " & Dow1 & " changed from " & Equip1 & " to " & Equip2
    
                'equip only because time change < minimum time to show-
                ElseIf ArvlTm1 > ArvlTm2 And 24 * 60 * (ArvlTm1 - ArvlTm2) < MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Changed from " & Equip1 & " to " & Equip2
                    
                'equip only because time change < minimum time to show-
                ElseIf ArvlTm2 > ArvlTm1 And 24 * 60 * (ArvlTm2 - ArvlTm1) < MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Changed from " & Equip1 & " to " & Equip2
                
                'time changed & equip changed
                 ElseIf ArvlTm1 > ArvlTm2 And 24 * 60 * (ArvlTm1 - ArvlTm2) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Arv time change from " & Format(ArvltTm1, "hhmm") & _
                        " to " & Format(ArvlTm2, "hhmm") & " changed from " & Equip1 & " to " & Equip2
                    
                'time changed & equip changed
                If ArvlTm2 > ArvlTm1 And 24 * 60 * (ArvlTm2 - ArvlTm1) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Arv time change from " & Format(ArvlTm1, "hhmm") & _
                        " to " & Format(ArvlTm2, "hhmm") & " changed from " & Equip1 & " to " & Equip2
                'End If
                
                If ArvlTm1 > ArvlTm2 Then Cells(RowY, 26) = -24 * 60 * (ArvlTm1 - ArvlTm2) Else Cells(RowY, 26) = 24 * 60 * (ArvlTm2 - ArvlTm1)
            End If

[U][B]***The entire Case Select section including the error section I show above. ********[/B][/U]

 Select Case TypeNow
        Case "Delete"
            OutputText = "Flt" & FltNbr1 & " Deleted " & Orgn1 & "-" & Dest1 & " " & Dow1
            
            
        Case "Add"
            If Orgn2 = HomeLoc Then
                OutputText = "Flt" & FltNbr2 & " Added " & Orgn2 & "-" & Dest2 & " dept " & Format(DeptTm2, "hhmm") & " " & Dow2
            Else
                OutputText = "Flt" & FltNbr2 & " Added " & Orgn2 & "-" & Dest2 & " arvl " & Format(ArvlTm2, "hhmm") & " " & Dow2
            End If
      
            
        Case "Time Changed"
            If Cells(RowY, 13) = HomeLoc Then
                If DeptTm1 = DeptTm2 Then OutputText = "" Else OutputText = "Flt" & FltNbr2 & " Dep time change from " & Format(DeptTm1, "hhmm") & " to " & Format(DeptTm2, "hhmm") & " " & Dow2
                If DeptTm1 > DeptTm2 Then Cells(RowY, 26) = 24 * 60 * (DeptTm1 - DeptTm2) Else Cells(RowY, 26) = -24 * 60 * (DeptTm2 - DeptTm1)
            Else
                If ArvlTm1 = ArvlTm2 Then OutputText = "" Else OutputText = "Flt" & FltNbr2 & " Arv time change from " & Format(ArvlTm1, "hhmm") & " to " & Format(ArvlTm2, "hhmm") & " " & Dow2
                If ArvlTm1 > ArvlTm2 Then Cells(RowY, 26) = -24 * 60 * (ArvlTm1 - ArvlTm2) Else Cells(RowY, 26) = 24 * 60 * (ArvlTm2 - ArvlTm1)
            End If
            
            
        Case "Days of Week"
                OutputText = "Flt" & FltNbr2 & " Ops days changed from " & Dow1 & " to " & Dow2
                
                
        Case "Equipment Change"
                OutputText = "Flt" & FltNbr2 & " " & Dow1 & " changed from " & Equip1 & " to " & Equip2
              
              
        Case "Days of Week, Time Changed"
            'is departure case-
            If Cells(RowY, 13) = HomeLoc Then
                If DeptTm1 = DeptTm2 Then
                    'DOW only changed
                    OutputText = "Flt" & FltNbr2 & " " & Dow1 & " changed to  " & Dow2
                
                'DOW only because time change < minimum time to show-
                ElseIf DeptTm1 > DeptTm2 And 24 * 60 * (DeptTm1 - DeptTm2) < MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Changed from " & Dow1 & " to " & Dow2
                    
                'DOW only because time change < minimum time to show-
                ElseIf DeptTm2 > DeptTm1 And 24 * 60 * (DeptTm2 - DeptTm1) < MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Changed from " & Dow1 & " to " & Dow2
                    
                'time changed & DOW
                ElseIf DeptTm1 > DeptTm2 And 24 * 60 * (DeptTm1 - DeptTm2) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Dep time change from " & Format(DeptTm1, "hhmm") & _
                        " to " & Format(DeptTm2, "hhmm") & " changed from " & Dow1 & " to " & Dow2
                    
                'time changed & DOW
                ElseIf DeptTm2 > DeptTm1 And 24 * 60 * (DeptTm2 - DeptTm1) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Dep time change from " & Format(DeptTm1, "hhmm") & _
                        " to " & Format(DeptTm2, "hhmm") & " changed from " & Dow1 & " to " & Dow2
                End If
                If DeptTm1 > DeptTm2 Then Cells(RowY, 26) = -24 * 60 * (DeptTm1 - DeptTm2) Else Cells(RowY, 26) = 24 * 60 * (DeptTm2 - DeptTm1)
            'is arrival case-
            Else
                If ArvlTm1 = ArvlTm2 Then
                    'DOW only changed
                    OutputText = "Flt" & FltNbr2 & " " & Dow1 & " changed to  " & Dow2
                
                'DOW only because time change < minimum time to show-
                ElseIf ArvlTm1 > ArvlTm2 And 24 * 60 * (ArvlTm1 - ArvlTm2) < MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Changed from " & Dow1 & " to " & Dow2
                    
                'DOW only because time change < minimum time to show-
                ElseIf ArvlTm2 > ArvlTm1 And 24 * 60 * (ArvlTm2 - ArvlTm1) < MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Changed from " & Dow1 & " to " & Dow2
                    
                'time changed & DOW
                ElseIf ArvlTm1 > ArvlTm2 And 24 * 60 * (ArvlTm1 - ArvlTm2) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Dep time change from " & Format(ArvlTm1, "hhmm") & _
                        " to " & Format(ArvlTm2, "hhmm") & " changed from " & Dow1 & " to " & Dow2
                    
                'time changed & DOW
                ElseIf ArvlTm2 > ArvlTm1 And 24 * 60 * (ArvlTm2 - ArvlTm1) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Dep time change from " & Format(ArvlTm1, "hhmm") & _
                        " to " & Format(ArvlTm2, "hhmm") & " changed from " & Dow1 & " to " & Dow2
                End If
                If ArvlTm1 > ArvlTm2 Then Cells(RowY, 26) = -24 * 60 * (ArvlTm1 - ArvlTm2) Else Cells(RowY, 26) = 24 * 60 * (ArvlTm2 - ArvlTm1)
            End If
            
            
        Case "Days of Week, Equip Change"
        'For DOW change & Equip Type
        If Dow1 = Dow2 Then
                OutputText = "Flt" & FltNbr2 & " Ops days changed from " & Dow1 & " to " & Dow2
                
                ElseIf Dow1 <> Dow2 Then OutputText = "Flt" & FltNbr2 & " Ops days changed from " & Dow1 & " to " & Dow2
        End If
                    
                    
        If RowY = 112 Then MsgBox ("Row112 reached")
        
        'ERROR CAUSING SECTION BELOW ------>>>>>>>>>
        Case "Time Changed, Equipment Type"
            If Cells(RowY, 13) = HomeLoc Then
            
                'departure case
                If DeptTm1 = DeptTm2 And Equip1 = Equip2 Then OutputText = ""
                
                If DeptTm1 = DeptTm2 Then
                'equip only changed
                    OutputText = "Flt" & FltNbr2 & " " & Dow1 & " changed from " & Equip1 & " to " & Equip2
    
                'equip only because time change < minimum time to show-
                ElseIf DeptTm1 > DeptTm2 And 24 * 60 * (DeptTm1 - DeptTm2) < MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Changed from " & Equip1 & " to " & Equip2
                    
                'equip only because time change < minimum time to show-
                ElseIf DeptTm2 > DeptTm1 And 24 * 60 * (DeptTm2 - DeptTm1) < MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Changed from " & Equip1 & " to " & Equip2
                    
                'time changed & equip changed
                ElseIf DeptTm1 > DeptTm2 And 24 * 60 * (DeptTm1 - DeptTm2) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Dep time change from " & Format(DeptTm1, "hhmm") & _
                        " to " & Format(DeptTm2, "hhmm") & " changed from " & Equip1 & " to " & Equip2
                    
                'time changed & equip changed
                ElseIf DeptTm2 > DeptTm1 And 24 * 60 * (DeptTm2 - DeptTm1) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Dep time change from " & Format(DeptTm1, "hhmm") & _
                        " to " & Format(DeptTm2, "hhmm") & " changed from " & Equip1 & " to " & Equip2
                'End If
                
                If DeptTm1 > DeptTm2 Then Cells(RowY, 26) = -24 * 60 * (DeptTm1 - DeptTm2) Else Cells(RowY, 26) = 24 * 60 * (DeptTm2 - DeptTm1)

            Else
            
                'arrival case
                If ArvlTm1 = ArvlTm2 And Equip1 = Equip2 Then OutputText = ""
                
                
                If ArvlTm1 = ArvlTm2 Then
                'equip only changed
                    OutputText = "Flt" & FltNbr2 & " " & Dow1 & " changed from " & Equip1 & " to " & Equip2
    
                'equip only because time change < minimum time to show-
                ElseIf ArvlTm1 > ArvlTm2 And 24 * 60 * (ArvlTm1 - ArvlTm2) < MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Changed from " & Equip1 & " to " & Equip2
                    
                'equip only because time change < minimum time to show-
                ElseIf ArvlTm2 > ArvlTm1 And 24 * 60 * (ArvlTm2 - ArvlTm1) < MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Changed from " & Equip1 & " to " & Equip2
                
                'time changed & equip changed
                 ElseIf ArvlTm1 > ArvlTm2 And 24 * 60 * (ArvlTm1 - ArvlTm2) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Arv time change from " & Format(ArvltTm1, "hhmm") & _
                        " to " & Format(ArvlTm2, "hhmm") & " changed from " & Equip1 & " to " & Equip2
                    
                'time changed & equip changed
                If ArvlTm2 > ArvlTm1 And 24 * 60 * (ArvlTm2 - ArvlTm1) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Arv time change from " & Format(ArvlTm1, "hhmm") & _
                        " to " & Format(ArvlTm2, "hhmm") & " changed from " & Equip1 & " to " & Equip2
                'End If
                
                If ArvlTm1 > ArvlTm2 Then Cells(RowY, 26) = -24 * 60 * (ArvlTm1 - ArvlTm2) Else Cells(RowY, 26) = 24 * 60 * (ArvlTm2 - ArvlTm1)
            End If
                        
'<<<-------------ERROR CAUSING SECTION ABOVE HERE
            
        
        'For changed Days of Week, Equip Type, Time Changed (3 elements): Updated 2-5-2019 1345L
        Case "Days of Week, Equipment Type, Time Changed"
            If Cells(RowY, 13) = HomeLoc Then
                'departure case
                'times the same though on departure time
                If DeptTm1 = DeptTm2 Then   'only indicating days of operation & equipment this sub-case
                    OutputText = "Flt" & FltNbr2 & " " & Dow1 & "to " & Dow2 & " changed from " & Equip1 & " to " & Equip2
                    
                ElseIf DeptTm1 > DeptTm2 And 24 * 60 * (DeptTm1 - DeptTm2) < MinTimeToShow Then
                     'will be Ops_day, Equip type change only
                    OutputText = "Flt" & FltNbr2 & Equip1 & " to " & Equip2 & " and ops days changed from " & _
                    Dow1 & " to " & Dow2
                
                ElseIf DeptTm2 > DeptTm1 And 24 * 60 * (DeptTm2 - DeptTm1) < MinTimeToShow Then
                    'will be Ops_day, Equip type change only
                    OutputText = "Flt" & FltNbr2 & Equip1 & " to " & Equip2 & " and ops days changed from " & _
                    Dow1 & " to " & Dow2
                                      
                ElseIf DeptTm1 > DeptTm2 And 24 * 60 * (DeptTm1 - DeptTm2) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Dep time change from " & Format(DeptTm1, "hhmm") & " to " & _
                    Format(DeptTm2, "hhmm") & ", changed from " & Equip1 & " to " & Equip2 & " and ops days changed from " & _
                    Dow1 & " to " & Dow2
                
                ElseIf DeptTm2 > DeptTm1 And 24 * 60 * (DeptTm2 - DeptTm1) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Dep time change from " & Format(DeptTm1, "hhmm") & " to " & _
                    Format(DeptTm2, "hhmm") & ", changed from " & Equip1 & " to " & Equip2 & " and ops days changed from " & _
                    Dow1 & " to " & Dow2
                End If
            Else
                'arrival case : Updated 2-6-2019 1400L
                'times the same though on departure time
                If ArvlTm1 = ArvlTm2 Then   'only indicating days of operation & equipment this sub-case
                    OutputText = "Flt" & FltNbr2 & " " & Dow1 & "to " & Dow2 & " changed from " & Equip1 & " to " & Equip2
                    
                ElseIf ArvlTm1 > ArvlTm2 And 24 * 60 * (ArvlTm1 - ArvlTm2) < MinTimeToShow Then
                    'will be Ops_day, Equip type change only
                    OutputText = "Flt" & FltNbr2 & Equip1 & " to " & Equip2 & " and ops days changed from " & _
                    Dow1 & " to " & Dow2
                
                 ElseIf ArvlTm2 > ArvlTm1 And 24 * 60 * (ArvlTm2 - ArvlTm1) < MinTimeToShow Then
                    'will be Ops_day, Equip type change only
                    OutputText = "Flt" & FltNbr2 & Equip1 & " to " & Equip2 & " and ops days changed from " & _
                    Dow1 & " to " & Dow2
                    
                ElseIf ArvlTm1 > ArvlTm2 And 24 * 60 * (ArvlTm1 - ArvlTm2) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Dep time change from " & Format(ArvlTm1, "hhmm") & " to " & _
                    Format(ArvlTm2, "hhmm") & ", changed from " & Equip1 & " to " & Equip2 & " and ops days changed from " & _
                    Dow1 & " to " & Dow2
                
                 ElseIf ArvlTm2 > ArvlTm1 And 24 * 60 * (ArvlTm2 - ArvlTm1) >= MinTimeToShow Then
                    OutputText = "Flt" & FltNbr2 & " Dep time change from " & Format(ArvlTm1, "hhmm") & " to " & _
                    Format(ArvlTm2, "hhmm") & ", changed from " & Equip1 & " to " & Equip2 & " and ops days changed from " & _
                    Dow1 & " to " & Dow2
                End If
                
                If ArvlTm1 > ArvlTm2 Then Cells(RowY, 26) = -24 * 60 * (ArvlTm1 - ArvlTm2) Else Cells(RowY, 26) = 24 * 60 * (ArvlTm2 - ArvlTm1)

            End If
                
    End Select      'ends the Case Select statement group.
 
Last edited by a moderator:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
The problem is probably with your If statements, but as I've no idea what you are trying to do, it's difficult to tell.
I'd suggest you start by uncommenting the 2 End If lines

Also when posting code please use code tags, the # icon in the reply window.
 
Last edited:
Upvote 0
"Case without Select Case"
This error message can be a red herring. The issue may not be with the case statement at all.
It usually denotes that you have some command that requires another command to close it. For example, you have an "If" than is missing and "End If". Or a "With" missing and "End With".
The fact that it mentions the "Case" usually means that the issue is contained in the code under the "Case" statement.
Based on all your IF statements, I would be willing to guess that you are missing and "End If" somewhere.

Try starting out with an empty Case statement, and then addi our code blocks in one step at a time, and see if they work. When the error pops up, you know that it was the last block you added that is the cause of the issue, and you can focus on that.
 
Upvote 0
Thank you. Yeah I was wondering if the error message wasn't really accurate. I've seen that before where there is an End If missing somewhere but you don't get the exact location of the missing End If.. Will try what you said. Thanks.
 
Upvote 0
Hi... Yes there is missing 'End If'... For instance, the 2nd If statement :

'departure case
If DeptTm1 = DeptTm2 And Equip1 = Equip2 Then OutputText = ""

does not have a End If...

This may solve your issue.
 
Upvote 0
Hi... Yes there is missing 'End If'... For instance, the 2nd If statement :

'departure case
If DeptTm1 = DeptTm2 And Equip1 = Equip2 Then OutputText = ""

does not have a End If...

This may solve your issue.
No, that is not it.

An IF...THEN statement where the part after the THEN is on the same line as the IF does NOT need and END IF. Not only does it not need it, it CANNOT have it.

The rule is this:
- Only use an END IF if the whole IF...THEN is put on multiple lines (and you are not using the line continuation character).
- If the entire statement is on one line, do not use an END IF (or if it would be one line when line continuation characters are considered)

For example, this would require an END IF:
Code:
    If Range("A1") = "Yes" Then
        MsgBox "Hello!"
    End If
while these two would not:
Code:
    If Range("A1") = "Yes" Then MsgBox "Hello!"
Code:
    If Range("A1") = "Yes" Then _
        MsgBox "Hello!"
 
Last edited:
Upvote 0
No, that is not it.

An IF...THEN statement where the part after the THEN is on the same line as the IF does NOT need and END IF. Not only does it not need it, it CANNOT have it.

The rule is this:
- Only use an END IF if the whole IF...THEN is put on multiple lines (and you are not using the line continuation character).
- If the entire statement is on one line, do not use an END IF (or if it would be one line when line continuation characters are considered)

For example, this would require an END IF:
Code:
    If Range("A1") = "Yes" Then
        MsgBox "Hello!"
    End If
while these two would not:
Code:
    If Range("A1") = "Yes" Then MsgBox "Hello!"
Code:
    If Range("A1") = "Yes" Then _
        MsgBox "Hello!"

oh... Great.. Thanks for this...

Sorry I was wrong in this.

However, there are couple of 'End If' statements which are commented.... Could be a reason for error
 
Upvote 0
However, there are couple of 'End If' statements which are commented.... Could be a reason for error
Yes, could be.

There is an awful lot going on there, so it may take some time to weed through it.
Personally, I do not like ElseIf statements and never use them. I find them a little confusing, and instead have the Else and If on separate lines. IMO, it just makes it easier to match up IF and END IFs.
 
Upvote 0

Forum statistics

Threads
1,223,956
Messages
6,175,613
Members
452,661
Latest member
Nonhle

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