My "If Not" Statement is Not Accomplishing My Desired Result

[Ark68]

I have this code:

Sub staffintegrity()
'Stop
    Dim rngpda As Range
    Dim rwpdaed As Long 'last row of pda range
    Dim bunm As String
    
'hide unstaffed crews
    For Each ws9 In wb_data.Worksheets
        Application.DisplayAlerts = False
        Debug.Print ws9.Name
        bunm = ws9.Name
        If ws9.Name Like "Sheet*" Then
            ws9.Delete
            Debug.Print bunm & " deleted."
            'Exit For
        ElseIf ws9.Name = "Services" Then
            ws9.Delete
            Debug.Print bunm & " deleted."
            'Exit For
        ElseIf ws9.Tab.Color = vbRed Then
            ws9.Visible = xlhiddensheet
            Debug.Print bunm & " hidden."
            'Exit For
        End If
        Application.DisplayAlerts = True
    Next ws9

    cntsh = 0 'count of visible sheets
    For Each ws9 In wb_data.Worksheets
        If ws9.Visible = xlSheetVisible Then
            If Not ws9.Name Like "EV*" Then
                cntsh = cntsh + 1
                With ws9
                    . . . <code> . . .
                End With
            End If
            ttsnr = ttsnr + tsnr
            MsgBox ccnt & " cells analysed in worksheet: " & ws9.Name & Chr(13) & tsnr & " invalid names were replaced.", vbInformation, "INTEGRITY: OK    Worksheet: " & ws9.Name
            tccnt = tccnt + ccnt
        End If
    Next ws9
    MsgBox tccnt & " cells analysed in " & cntsh & " worksheets." & Chr(13) & ttsnr & " invalid names were replaced.", vbInformation, "INTEGRITY: OK    Worbkbook: " & wb_data.Name
End Sub

In an effort to exclude worksheets named "EVE1", "EVE", EVL", I added the if/endif in blue. But this doesn't appear to be working as those sheets are still being included.

 

[RoryA]

That line would exclude the sheets you mentioned, so something else must be going on. Also xlhiddensheet is not actually an Excel constant. It should be xlsheethidden

 

[Ark68]

Thanks Rory.
Interesting that you pointed out the constant mistake. I would have thought it would have thrown an error. Turns out I had previously hidden the red tabbed sheets, but this code provided still ran without an error even though it encountered the wrong constant.

As far as the original problem? No idea. I thought possibly I had a leading space in the tab names, but not the case. So far, including those sheets hasn't resulted in any problems, but I can see potential.

 

[RoryA]

A missing constant will be treated as an empty variable. When assigning that to a property expecting a number, it will be coerced to 0 which happens to be the same as the value of xlSheetHidden in this case.

Where/how are you determining that those tabs are being processed?

 

[Ark68]

Hi Rory,
Here is my full code. The code in purple should not execute for any sheets with an name beginning with "EV"
In my workbook, I have worksheets named "EVE", "EVE1", "EVE2", "EVL", EVL1" and "EVL2". All but EVE are hidden because their tab colours are red.
When the code reaches workbook EVE, the purple code is executed but I wouldn't expect it to.

Sub staffintegrity()
'Stop
    Dim rngpda As Range
    Dim rwpdaed As Long 'last row of pda range
    Dim bunm As String
   
'hide unstaffed crews
    rwpdast = 13
   
    For Each ws9 In wb_data.Worksheets
        Application.DisplayAlerts = False
        Debug.Print ws9.Name
        bunm = ws9.Name
        If ws9.Name Like "Sheet*" Then
            ws9.Delete
            Debug.Print bunm & " deleted."
            'Exit For
        ElseIf ws9.Name = "Services" Then
            ws9.Delete
            Debug.Print bunm & " deleted."
            'Exit For
        ElseIf ws9.Tab.Color = vbRed Then
            ws9.Visible = xlSheetHidden
            Debug.Print bunm & " hidden."
            'Exit For
        End If
        Application.DisplayAlerts = True
    Next ws9
'only active crews populate, no remnent of previous working worksheet
'check PDA range of each sheet to ensure names are on roster
'Stop
    cntsh = 0 'count of visible sheets
    For Each ws9 In wb_data.Worksheets
        If ws9.Visible = xlSheetVisible Then
            If Not ws9.Name Like "EV*" Then
                cntsh = cntsh + 1
                With ws9
                    ccnt = 0 'cell count
                    tsnr = 0 'total replaced in sheet
                    'MsgBox "Analysing: " & ws9.Name
                    Debug.Print "Analysing: " & ws9.Name
                    ws9.Activate
                    rwpdaed = Application.WorksheetFunction.Match("Facility Maintenance Activities", .Columns(1), 0) - 3
                    Set rngpda = ws9.Range("H13:Q" & rwpdaed)
                    For Each cell In rngpda
                        ccnt = ccnt + 1
                        cval2 = cell.Value
                        Debug.Print cell.Address & " : " & cval2
                        If cval2 = "" Then
                            Debug.Print "Service ignored: Empty"
                        ElseIf Right(cval2, 3) = " AM" Or Right(cval2, 3) = " PM" Then
                            Debug.Print "Pre-service ignored: " & cval2
                        ElseIf IsDate(Format(Now(), "yyyy-mm-dd ") & cval2) = True Then
                            Debug.Print "Service ignored (time): " & cval2
                        ElseIf cval2 = "NA" Or Right(cval2, 3) = "NR" Then
                            Debug.Print "Service ignored: " & cval2
                        ElseIf cval2 = "AUTO" Or cval2 = "USER" Then
                            Debug.Print "Service ignored: " & cval2
                        Else
                            cntcval2 = Application.WorksheetFunction.CountIf(ws_staff.Columns(4), cval2)
                            If cntcval2 > 0 Then
                                Debug.Print "Match: " & cval2 & " [" & l & "]"
                            Else
                                MsgBox cval2 & " at " & cell.Address & " is not in the roster." & Chr(13) & "Please adjust the name to an employee that is working", vbCritical, "Error: Staff mismatch"
                                cell.Font.Color = vbRed
                                frm_sniroster.Show
                                tsnr = tsnr + cntrpl
                            End If
                        End If
                    Next cell
                End With
            End If
            ttsnr = ttsnr + tsnr
            MsgBox ccnt & " cells analysed in worksheet: " & ws9.Name & Chr(13) & tsnr & " invalid names were replaced.", vbInformation, "INTEGRITY: OK    Worksheet: " & ws9.Name
            tccnt = tccnt + ccnt
        End If
    Next ws9
    'Stop
    MsgBox tccnt & " cells analysed in " & cntsh & " worksheets." & Chr(13) & ttsnr & " invalid names were replaced.", vbInformation, "INTEGRITY: OK    Worbkbook: " & wb_data.Name
End Sub

 

[RoryA]

Add a msgbox len(ws9.name) statement and check it returns 3

 

[Ark68]

As suggested Rory I added that diagnostic line.
All, including "EVE" came back as 3.

 

[RoryA]

Can you put a workbook somewhere? No data required.

 

[Ark68]

Will this do?

 

[RoryA]

Not really as it doesn't appear to be the workbook with the relevant sheets in it...