Type Mismatch with Sheet Name VBA

Question

I'm trying to write a simple VBA code that runs through a workbook, deletes some sheets complete, and clears the contents of others. This is my code:

Sub CleanSheets()
Dim x As Integer

x = 1
Do Until x = Sheets.Count
    If Sheets(x).Name = "MIR - Raw" Or "MRR - Raw" Or "MWR - Raw" Or "PL - Raw" Or "SHP - Raw" Then
    'clear them
        Sheets(x).ClearContents
    ElseIf Sheets(x).Name = "Dashboard" Then

    Else
        'delete the sheet
        Application.DisplayAlerts = False
        Sheets(x).Delete
        Application.DisplayAlerts = True
        x = x - 1
    End If
x = x + 1
Loop
End Sub

I keep getting a type mismatch on the "if sheets(x).name = ..." line. I'm not too familiar with the Sheets.Names object, how am I screwing up the type here?

try it with If Sheets(x).Name = "MIR - Raw" Or Sheets(x).Name = "MRR - Raw" Or ... etc. — Daniel Dušek, May 03 '14 at 22:56
"MIR - Raw" Or "MRR - Raw" can't work because 'Or' expects expressions which can be evaluated to bool values, then it performs logical disjunction. Or the 'Or' operator also performs a bitwise comparison of identically positioned bits in two numeric expressions and sets the corresponding bit in result. But you have used strings therefore the type mismatch. — Daniel Dušek, May 03 '14 at 23:14
basicaly your code says : If Sheets(x).Name = "MIR - Raw" Or "MRR - Raw"=true Or "MWR - Raw"=true Or "PL - Raw"=true Or "SHP - Raw"=true Then.... , and you cant compare a string with a boolean — Patrick Lepelletier, May 04 '14 at 10:10

score 2 · Answer 1 · edited May 23 '17 at 12:05

@dee's answer has resolved your issue, but as I see you are checking too many names, and I would like to provide a more elegant solution to your problem.

The idea is very simple: dump the "If" and the "else if" cases in two separate arrays, and check if the sheet name is in each array, and take the corresponding action. Since there is no action in the ElseIf clause, we can rewrite the logic with If/ElseIf only. I utilize Jimmy Pena's function IsInArray in this answer to make the check.

Also, I have made a few changes in the code, to make it a little more readable:

Sub CleanSheets()
    Dim x As Long   ' Long is safer and faster
    Dim aFirstCategory(4) As String
    Dim aSecondCategory(1) As String

    ' Note that checking for the sheet name is usually not very robust.
    ' You might be interested in the Like function to make your checks more flexible
    aFirstCategory(0) = "MIR - Raw"
    aFirstCategory(1) = "MRR - Raw"
    aFirstCategory(2) = "MWR - Raw"
    aFirstCategory(3) = "PL - Raw"
    aFirstCategory(4) = "SHP - Raw"

    aSecondCategory(0) = "Dahshboard"

    x = 1  ' I would check with ForEach wSheet in Sheets instead
    Do 'Until should go to the end of the loop, else the last sheet will not be checked
        If IsInArray(Sheets(x).Name, aFirstCategory) Then
        'clear them
            Sheets(x).Cells.ClearContents ' Sheets(x).ClearContents gives error I think
        ElseIf not IsInArray(Sheets(x).Name, aSecondCategory) Then
            'delete the sheet
            Application.DisplayAlerts = False
            Sheets(x).Delete
            Application.DisplayAlerts = True
            x = x - 1
        End If
        x = x + 1
    Loop Until x = Sheets.Count
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

score 2 · Accepted Answer · answered May 04 '14 at 02:48

Your code will work fine. Just you need to change the If Sheets(x).Name = "MIR - Raw" Or "MRR - Raw" Or "MWR - Raw" Or "PL - Raw" Or "SHP - Raw" to If Sheets(x).Name = "MIR - Raw" Or Sheets(x).Name = "MRR - Raw" Or Sheets(x).Name = "MWR - Raw" Or Sheets(x).Name = "PL - Raw" Or Sheets(x).Name = "SHP - Raw"

Sub CleanSheets()
Dim x As Integer

x = 1
Do Until x = Sheets.Count
    If Sheets(x).Name = "MIR - Raw" Or Sheets(x).Name = "MRR - Raw" Or Sheets(x).Name = "MWR - Raw" Or Sheets(x).Name = "PL - Raw" Or Sheets(x).Name = "SHP - Raw" Then
    'clear them
        Sheets(x).ClearContents
    ElseIf Sheets(x).Name = "Dashboard" Then

    Else
        'delete the sheet
        Application.DisplayAlerts = False
        Sheets(x).Delete
        Application.DisplayAlerts = True
        x = x - 1
    End If
x = x + 1
Loop
End Sub

Type Mismatch with Sheet Name VBA

2 Answers2