Unable to print correct message based on conditional if...elseif...else

Question

My current code is below. In other Subs, I have .UsedRange.Delete available for each sheet depending on circumstances. However, even when all "Base" and "Corr" sheets are empty, I always get the MsgBox for "Too many Base sheets..."

Does anyone see what I'm missing?

Code Updated

Sub ViewResults()

    'Counts number of empty "Base" sheets'
    
    Dim i As Integer
    Dim b_empty As Integer
    
    b_empty = 0
    
    If IsEmpty(Worksheets("Calypso (Zeiss) Base")) = True Then
                
        b_empty = b_empty + 1
            
    End If
    
    If IsEmpty(Worksheets("Camio (LK) Base")) = True Then
                
        b_empty = b_empty + 1
            
    End If
    
    If IsEmpty(Worksheets("Camio (Nikon) Base")) = True Then
                
        b_empty = b_empty + 1
            
    End If
    
    If IsEmpty(Worksheets("GOM (Blue Light) Base")) = True Then
                
        b_empty = b_empty + 1
            
    End If
    
    If IsEmpty(Worksheets("PC-DMIS (Global) Base")) = True Then
                
        b_empty = b_empty + 1
            
    End If
    
    If IsEmpty(Worksheets("Quindos (HTA) Base")) = True Then
                
        b_empty = b_empty + 1
            
    End If

    
    'Counts how many cempties are True'
    
    Dim j As Integer
    Dim c_empty As Integer
    
    c_empty = 0
    
    If IsEmpty(Worksheets("Calypso (Zeiss) Corr")) = True Then
                
        c_empty = c_empty + 1
            
    End If
    
    If IsEmpty(Worksheets("Camio (LK) Corr")) = True Then
                
        c_empty = c_empty + 1
            
    End If
    
    If IsEmpty(Worksheets("Camio (Nikon) Corr")) = True Then
                
        c_empty = c_empty + 1
            
    End If
    
    If IsEmpty(Worksheets("GOM (Blue Light) Corr")) = True Then
                
        c_empty = c_empty + 1
            
    End If
    
    If IsEmpty(Worksheets("PC-DMIS (Global) Corr")) = True Then
                
        c_empty = c_empty + 1
            
    End If
    
    If IsEmpty(Worksheets("Quindos (HTA) Corr")) = True Then
                
        c_empty = c_empty + 1
            
    End If
    
    
    'Test for correct number of non-empty base&corr sheets'
    
    If b_empty > 5 Then
    
        MsgBox "Base Data seems to be missing. Please Import Base Data.", vbOKOnly, "Missing Base Data"
    
    End If
    
    If b_empty < 5 Then
    
        MsgBox "Too many Base sheets contain data. Please try again to Import Base Data.", vbOKOnly, "Too Much Base Data"
        
    End If
        
    If b_empty = 5 And c_empty > 5 Then
    
        MsgBox "Corr Data seems to be missing. Please Import Corr Data.", vbOKOnly, "Missing Corr Data"
    
    End If
        
    If b_empty = 5 And c_empty < 5 Then
    
        MsgBox "Too many Corr sheets contain data. Please try again to Import Corr Data.", vbOKOnly, "Too Much Corr Data"
    
    End If
    
    If b_empty = 5 And c_empty = 5 Then

        Worksheets("Data Correlation").Activate
        
    End If
        
    On Error GoTo EmailSupport
    
    Exit Sub

I originally had .cells.clear on all sheets in another Sub, but thought maybe there were residual objects, so changed all cases of .cells.clear to .usedrange.delete.

UPDATE: The code has been changed to ask the worksheets individually if they are empty and to update the count if True. Still getting the same MsgBox from condition 2

Darren Bartrup-Cook · Accepted Answer · 2023-08-10T13:43:20.450

IsEmpty

IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants.

bempties(i)is defined as an object and you've filled it with references to worksheets in the currently active workbook, so it will never be empty. You could check If bempties(i) is nothing then but you've set each instance to a worksheet, so they'll never be nothing either. After running the check b_empty will always equal 0.

The upper bound of bempties will always be 6 as you've set it to that.
b_empty < (UBound(bempties) - 1) or 0 < 5 will always be true and display the message.

Are you trying to check if the sheet is empty? i.e. No data on the sheet?
This code might help.

Note - I've used UsedRange to find the last cell - see the accepted answer on my last link for better ways and why not to use UsedRange.

Public Sub ViewResults()

    Dim wrkSht As Worksheet
    Dim BaseCount As Long, BaseEmpty As Long
    Dim CorrCount As Long, CorrEmpty As Long
    
    'ThisWorkbook is the book containing this code.
    For Each wrkSht In ThisWorkbook.Worksheets
        'Does Base or Corr appear in the sheet name?
        If InStr(wrkSht.Name, "Base") > 0 Then
            BaseCount = BaseCount + 1
            'There are better ways to find the last used cell in sheet and you'll still need to check if A1 contains data.
            If wrkSht.UsedRange.Address = "$A$1" Then BaseEmpty = BaseEmpty + 1
        ElseIf InStr(wrkSht.Name, "Corr") > 0 Then
            CorrCount = CorrCount + 1
            If wrkSht.UsedRange.Address = "$A$1" Then CorrEmpty = CorrEmpty + 1
        End If
    Next wrkSht
    
    MsgBox "Base sheets: " & BaseCount & vbCr & _
           "Empty base sheets: " & BaseEmpty & vbCr & vbCr & _
           "Corr sheets: " & CorrCount & vbCr & _
           "Empty Corr sheets: " & CorrEmpty, vbOKOnly + vbInformation

End Sub

Further reading:
For Each...Next statement
InStr function
Find last used cell in Excel VBA

In other words, setting the IsEmpty() to bempties(i) does not pass it off to cycle through all the sheets within bempties? Yes, I am trying to check if a sheet is empty without having to code each sheet individually. — Cameron South, Aug 10 '23 at 13:21
@CameronSouth Have added some code that might point you in the right direction. — Darren Bartrup-Cook, Aug 10 '23 at 13:43
The logic you report is sound, however, there's no guarantee the alleged emptiness of any one cell is indicative of the entire sheet's emptiness. One "Base" and one "Corr" sheet will at any given point in time have an .xlsx or .csv file uploaded to it by another Sub by a simple copy/paste method, but these reports vary greatly based on their originating software. Hence why I used `.UsedRange.Delete` to get rid of anything and everything (some reports even include shapes). That being said, could I not just replace the usage of `.UsedRange.Address` in your code with `.IsEmpty = True` ? — Cameron South, Aug 10 '23 at 15:36
I used your code, then augmented with this guy's solution: https://www.mrexcel.com/board/threads/check-to-see-if-a-worksheet-is-blank-in-vba.22474/ Thanks for all your help! — Cameron South, Aug 10 '23 at 16:30
@CameronSouth That's a name I haven't seen in years. XL-Dennis answered a fair few of my questions when I was starting out. `IsEmpty` wouldn't work as it checks variables rather than the contents of a worksheet. — Darren Bartrup-Cook, Aug 11 '23 at 07:01

Unable to print correct message based on conditional if...elseif...else

1 Answers1

Linked