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

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

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

问题

以下是代码部分的翻译：

  1. Sub ViewResults()
  3.     '统计空的“Base”工作表数量
  4.     
  5.     Dim i As Integer
  6.     Dim b_empty As Integer
  7.     
  8.     b_empty = 0
  9.     
  10.     If IsEmpty(Worksheets("Calypso (Zeiss) Base")) = True Then
  11.                 
  12.         b_empty = b_empty + 1
  13.             
  14.     End If
  15.     
  16.     If IsEmpty(Worksheets("Camio (LK) Base")) = True Then
  17.                 
  18.         b_empty = b_empty + 1
  19.             
  20.     End If
  21.     
  22.     If IsEmpty(Worksheets("Camio (Nikon) Base")) = True Then
  23.                 
  24.         b_empty = b_empty + 1
  25.             
  26.     End If
  27.     
  28.     If IsEmpty(Worksheets("GOM (Blue Light) Base")) = True Then
  29.                 
  30.         b_empty = b_empty + 1
  31.             
  32.     End If
  33.     
  34.     If IsEmpty(Worksheets("PC-DMIS (Global) Base")) = True Then
  35.                 
  36.         b_empty = b_empty + 1
  37.             
  38.     End If
  39.     
  40.     If IsEmpty(Worksheets("Quindos (HTA) Base")) = True Then
  41.                 
  42.         b_empty = b_empty + 1
  43.             
  44.     End If
  46.     
  47.     '统计为True的空工作表数量
  48.     
  49.     Dim j As Integer
  50.     Dim c_empty As Integer
  51.     
  52.     c_empty = 0
  53.     
  54.     If IsEmpty(Worksheets("Calypso (Zeiss) Corr")) = True Then
  55.                 
  56.         c_empty = c_empty + 1
  57.             
  58.     End If
  59.     
  60.     If IsEmpty(Worksheets("Camio (LK) Corr")) = True Then
  61.                 
  62.         c_empty = c_empty + 1
  63.             
  64.     End If
  65.     
  66.     If IsEmpty(Worksheets("Camio (Nikon) Corr")) = True Then
  67.                 
  68.         c_empty = c_empty + 1
  69.             
  70.     End If
  71.     
  72.     If IsEmpty(Worksheets("GOM (Blue Light) Corr")) = True Then
  73.                 
  74.         c_empty = c_empty + 1
  75.             
  76.     End If
  77.     
  78.     If IsEmpty(Worksheets("PC-DMIS (Global) Corr")) = True Then
  79.                 
  80.         c_empty = c_empty + 1
  81.             
  82.     End If
  83.     
  84.     If IsEmpty(Worksheets("Quindos (HTA) Corr")) = True Then
  85.                 
  86.         c_empty = c_empty + 1
  87.             
  88.     End If
  89.     
  90.     
  91.     '检查非空的“Base”和“Corr”工作表数量是否正确
  92.     
  93.     If b_empty > 5 Then
  94.     
  95.         MsgBox "Base Data seems to be missing. Please Import Base Data.", vbOKOnly, "Missing Base Data"
  96.     
  97.     End If
  98.     
  99.     If b_empty < 5 Then
  100.     
  101.         MsgBox "Too many Base sheets contain data. Please try again to Import Base Data.", vbOKOnly, "Too Much Base Data"
  102.         
  103.     End If
  104.         
  105.     If b_empty = 5 And c_empty > 5 Then
  106.     
  107.         MsgBox "Corr Data seems to be missing. Please Import Corr Data.", vbOKOnly, "Missing Corr Data"
  108.     
  109.     End If
  110.         
  111.     If b_empty = 5 And c_empty < 5 Then
  112.     
  113.         MsgBox "Too many Corr sheets contain data. Please try again to Import Corr Data.", vbOKOnly, "Too Much Corr Data"
  114.     
  115.     End If
  116.     
  117.     If b_empty = 5 And c_empty = 5 Then
  119.         Worksheets("Data Correlation").Activate
  120.         
  121.     End If
  122.         
  123.     On Error GoTo EmailSupport
  124.     
  125.     Exit Sub
  126. 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

  1. Sub ViewResults()
  3.     &#39;Counts number of empty &quot;Base&quot; sheets&#39;
  4.     
  5.     Dim i As Integer
  6.     Dim b_empty As Integer
  7.     
  8.     b_empty = 0
  9.     
  10.     If IsEmpty(Worksheets(&quot;Calypso (Zeiss) Base&quot;)) = True Then
  11.                 
  12.         b_empty = b_empty + 1
  13.             
  14.     End If
  15.     
  16.     If IsEmpty(Worksheets(&quot;Camio (LK) Base&quot;)) = True Then
  17.                 
  18.         b_empty = b_empty + 1
  19.             
  20.     End If
  21.     
  22.     If IsEmpty(Worksheets(&quot;Camio (Nikon) Base&quot;)) = True Then
  23.                 
  24.         b_empty = b_empty + 1
  25.             
  26.     End If
  27.     
  28.     If IsEmpty(Worksheets(&quot;GOM (Blue Light) Base&quot;)) = True Then
  29.                 
  30.         b_empty = b_empty + 1
  31.             
  32.     End If
  33.     
  34.     If IsEmpty(Worksheets(&quot;PC-DMIS (Global) Base&quot;)) = True Then
  35.                 
  36.         b_empty = b_empty + 1
  37.             
  38.     End If
  39.     
  40.     If IsEmpty(Worksheets(&quot;Quindos (HTA) Base&quot;)) = True Then
  41.                 
  42.         b_empty = b_empty + 1
  43.             
  44.     End If
  46.     
  47.     &#39;Counts how many cempties are True&#39;
  48.     
  49.     Dim j As Integer
  50.     Dim c_empty As Integer
  51.     
  52.     c_empty = 0
  53.     
  54.     If IsEmpty(Worksheets(&quot;Calypso (Zeiss) Corr&quot;)) = True Then
  55.                 
  56.         c_empty = c_empty + 1
  57.             
  58.     End If
  59.     
  60.     If IsEmpty(Worksheets(&quot;Camio (LK) Corr&quot;)) = True Then
  61.                 
  62.         c_empty = c_empty + 1
  63.             
  64.     End If
  65.     
  66.     If IsEmpty(Worksheets(&quot;Camio (Nikon) Corr&quot;)) = True Then
  67.                 
  68.         c_empty = c_empty + 1
  69.             
  70.     End If
  71.     
  72.     If IsEmpty(Worksheets(&quot;GOM (Blue Light) Corr&quot;)) = True Then
  73.                 
  74.         c_empty = c_empty + 1
  75.             
  76.     End If
  77.     
  78.     If IsEmpty(Worksheets(&quot;PC-DMIS (Global) Corr&quot;)) = True Then
  79.                 
  80.         c_empty = c_empty + 1
  81.             
  82.     End If
  83.     
  84.     If IsEmpty(Worksheets(&quot;Quindos (HTA) Corr&quot;)) = True Then
  85.                 
  86.         c_empty = c_empty + 1
  87.             
  88.     End If
  89.     
  90.     
  91.     &#39;Test for correct number of non-empty base&amp;corr sheets&#39;
  92.     
  93.     If b_empty &gt; 5 Then
  94.     
  95.         MsgBox &quot;Base Data seems to be missing. Please Import Base Data.&quot;, vbOKOnly, &quot;Missing Base Data&quot;
  96.     
  97.     End If
  98.     
  99.     If b_empty &lt; 5 Then
  100.     
  101.         MsgBox &quot;Too many Base sheets contain data. Please try again to Import Base Data.&quot;, vbOKOnly, &quot;Too Much Base Data&quot;
  102.         
  103.     End If
  104.         
  105.     If b_empty = 5 And c_empty &gt; 5 Then
  106.     
  107.         MsgBox &quot;Corr Data seems to be missing. Please Import Corr Data.&quot;, vbOKOnly, &quot;Missing Corr Data&quot;
  108.     
  109.     End If
  110.         
  111.     If b_empty = 5 And c_empty &lt; 5 Then
  112.     
  113.         MsgBox &quot;Too many Corr sheets contain data. Please try again to Import Corr Data.&quot;, vbOKOnly, &quot;Too Much Corr Data&quot;
  114.     
  115.     End If
  116.     
  117.     If b_empty = 5 And c_empty = 5 Then
  119.         Worksheets(&quot;Data Correlation&quot;).Activate
  120.         
  121.     End If
  122.         
  123.     On Error GoTo EmailSupport
  124.     
  125.     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。

  1. Public Sub ViewResults()
  3.     Dim wrkSht As Worksheet
  4.     Dim BaseCount As Long, BaseEmpty As Long
  5.     Dim CorrCount As Long, CorrEmpty As Long
  6.     
  7.     'ThisWorkbook是包含此代码的工作簿。
  8.     For Each wrkSht In ThisWorkbook.Worksheets
  9.         'Base或Corr是否出现在工作表名称中？
  10.         If InStr(wrkSht.Name, "Base") > 0 Then
  11.             BaseCount = BaseCount + 1
  12.             '有更好的方法来查找工作表中的最后一个使用的单元格，您仍然需要检查A1是否包含数据。
  13.             If wrkSht.UsedRange.Address = "$A$1" Then BaseEmpty = BaseEmpty + 1
  14.         ElseIf InStr(wrkSht.Name, "Corr") > 0 Then
  15.             CorrCount = CorrCount + 1
  16.             If wrkSht.UsedRange.Address = "$A$1" Then CorrEmpty = CorrEmpty + 1
  17.         End If
  18.     Next wrkSht
  19.     
  20.     MsgBox "Base sheets: " & BaseCount & vbCr & _
  21.            "Empty base sheets: " & BaseEmpty & vbCr & vbCr & _
  22.            "Corr sheets: " & CorrCount & vbCr & _
  23.            "Empty Corr sheets: " & CorrEmpty, vbOKOnly + vbInformation
  25. 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.

  1. Public Sub ViewResults()
  3.     Dim wrkSht As Worksheet
  4.     Dim BaseCount As Long, BaseEmpty As Long
  5.     Dim CorrCount As Long, CorrEmpty As Long
  6.     
  7.     &#39;ThisWorkbook is the book containing this code.
  8.     For Each wrkSht In ThisWorkbook.Worksheets
  9.         &#39;Does Base or Corr appear in the sheet name?
  10.         If InStr(wrkSht.Name, &quot;Base&quot;) &gt; 0 Then
  11.             BaseCount = BaseCount + 1
  12.             &#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.
  13.             If wrkSht.UsedRange.Address = &quot;$A$1&quot; Then BaseEmpty = BaseEmpty + 1
  14.         ElseIf InStr(wrkSht.Name, &quot;Corr&quot;) &gt; 0 Then
  15.             CorrCount = CorrCount + 1
  16.             If wrkSht.UsedRange.Address = &quot;$A$1&quot; Then CorrEmpty = CorrEmpty + 1
  17.         End If
  18.     Next wrkSht
  19.     
  20.     MsgBox &quot;Base sheets: &quot; &amp; BaseCount &amp; vbCr &amp; _
  21.            &quot;Empty base sheets: &quot; &amp; BaseEmpty &amp; vbCr &amp; vbCr &amp; _
  22.            &quot;Corr sheets: &quot; &amp; CorrCount &amp; vbCr &amp; _
  23.            &quot;Empty Corr sheets: &quot; &amp; CorrEmpty, vbOKOnly + vbInformation
  25. 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
  • arrays
  • excel
  • if-statement
  • vba
匿名

发表评论

匿名网友

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

确定