英文:
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()
'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
答案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 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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论