excel - 找不到对象时如何进行？

Question

我正在寻找表格中的空白单元格。当没有空白单元格时想要有一个消息或运行一个命令。我尝试了以下版本，但没有一个有效

Sub Macro1() 
    ActiveSheet.ListObjects("Tabel1").DataBodyRange.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    On Error GoTo Line1
Line1:
    MsgBox "no blank cell is found"
End Sub

还有这个

Sub Macro1()
    ActiveSheet.ListObjects("Tabel1").DataBodyRange.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    If Selection = "" Then
        MsgBox "no blank cell is found"
    End If
End Sub

Answer 1

我建议捕捉错误并检查 BlankCells是 Nothing .

Sub Macro1() 
    Dim BlankCells As Range
    On Error Resume Next 'supress all error messages until …Goto 0
    Set BlankCells = ActiveSheet.ListObjects("Tabel1").DataBodyRange.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0 'never forget to re-activate error reporting immedeately!

    If BlankCells Is Nothing Then
        MsgBox "no blank cell is found"
    Else
        MsgBox BlankCells.Cells.Count & " blank cell(s) found"
    End If
End Sub

您可能会从阅读中受益……

How to avoid using Select in Excel VBA .

VBA Error Handling – A Complete Guide