无法根据条件if…elseif…else打印正确的消息。

huangapple go评论137阅读模式
英文:

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

问题

以下是代码部分的翻译:

Sub ViewResults()

    '统计空的“Base”工作表数量
    
    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

    
    '统计为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
    
    
    '检查非空的“Base”和“Corr”工作表数量是否正确
    
    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
End Sub

希望这对你有所帮助。如果有任何其他翻译需求,请随时告诉我。

英文:

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()

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

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

        Worksheets(&quot;Data Correlation&quot;).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

答案1

得分: 1

IsEmpty函数在以下情况下返回True:如果变量未初始化或明确设置为Empty;否则返回False。如果表达式包含多个变量,则始终返回False。IsEmpty仅对变体类型的变量返回有意义的信息。

bempties(i)被定义为一个对象,并且您已经用活动工作簿中的工作表引用填充了它,因此它永远不会为空。您可以检查If bempties(i) is nothing then,但是您已将每个实例设置为工作表,因此它们也永远不会为空。运行检查后,b_empty将始终等于0。

bempties的上限始终为6,因为您将其设置为6。

b_empty < (UBound(bempties) - 1)0 < 5始终为真,并显示该消息。

您是想要检查工作表是否为空吗?即工作表上没有数据?此代码可能有所帮助。

注意 - 我使用了UsedRange来查找最后一个单元格 - 请参考我的上一个链接上的被接受的答案,了解更好的方法以及为什么不要使用UsedRange

Public Sub ViewResults()

    Dim wrkSht As Worksheet
    Dim BaseCount As Long, BaseEmpty As Long
    Dim CorrCount As Long, CorrEmpty As Long
    
    'ThisWorkbook是包含此代码的工作簿。
    For Each wrkSht In ThisWorkbook.Worksheets
        'Base或Corr是否出现在工作表名称中?
        If InStr(wrkSht.Name, "Base") > 0 Then
            BaseCount = BaseCount + 1
            '有更好的方法来查找工作表中的最后一个使用的单元格,您仍然需要检查A1是否包含数据。
            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

进一步阅读:

英文:

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 &lt; (UBound(bempties) - 1) or 0 &lt; 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
    
    &#39;ThisWorkbook is the book containing this code.
    For Each wrkSht In ThisWorkbook.Worksheets
        &#39;Does Base or Corr appear in the sheet name?
        If InStr(wrkSht.Name, &quot;Base&quot;) &gt; 0 Then
            BaseCount = BaseCount + 1
            &#39;There are better ways to find the last used cell in sheet and you&#39;ll still need to check if A1 contains data.
            If wrkSht.UsedRange.Address = &quot;$A$1&quot; Then BaseEmpty = BaseEmpty + 1
        ElseIf InStr(wrkSht.Name, &quot;Corr&quot;) &gt; 0 Then
            CorrCount = CorrCount + 1
            If wrkSht.UsedRange.Address = &quot;$A$1&quot; Then CorrEmpty = CorrEmpty + 1
        End If
    Next wrkSht
    
    MsgBox &quot;Base sheets: &quot; &amp; BaseCount &amp; vbCr &amp; _
           &quot;Empty base sheets: &quot; &amp; BaseEmpty &amp; vbCr &amp; vbCr &amp; _
           &quot;Corr sheets: &quot; &amp; CorrCount &amp; vbCr &amp; _
           &quot;Empty Corr sheets: &quot; &amp; CorrEmpty, vbOKOnly + vbInformation

End Sub  

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

huangapple
  • 本文由 发表于 2023年8月10日 19:11:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/76875175.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定