Select case not working

[JackDanIce]

Hi,

Please can someone help me understand why this code for a select case isn't working, it's skipping straight to the Case Else line - the worksheets with the case names do exist in the workbook as they have been created by another macro and the cases match too:

Sub CountRows()
Dim ws As Worksheet
For Each ws In Worksheets
    Select Case ws.Name
        Case "Remaining", "No_Break", "Listed_Instruments", "Net_Zero_Difference", _
                "One_Dodge_Many_FO", "Many_Dodge_One_FO"
            If IsEmpty(Range("A3")) Then
                Range("A1").Value = "No breaks"
            Else
                Range("A1").Value = Range("A2").End(xlDown).Row - 2
            End If
        Case Else
    End Select
Next ws
End Sub

Thank you,
Jack

 

[SamTYler]

It works in mine. Sorry.

 

[JackDanIce]

I know exactly what I did wrong and was just about to remove this post!
Basically I wasn't specifying each worksheet to check if A3 is empty or not so it was checking A3 in whatever the activesheet is without adjusting for the next sheet - no worries, problem solved!

 

[lenze]

Select Case is "Case Sensitve". So, "Remaining" is not the same as "REMAINING". I would normally use

Select Case UCase(ws.Name)
Case "REMAINING","NO_BREAK",'etc

lenze

 

[JackDanIce]

Cheers Lenze, fortunately this time the cases did all match, just a oversight in my part on what value was being (incorrectly) valued in the IF statement, but good to know for next time!

 

[Scott Huish]

You also don't need the Case Else as you are not executing any code under this.

 

[JackDanIce]

Thanks HotPepper, I think I had something there before that I decided I didn't need but didn't delete the case else part out.
Cheers,
J