How to proceed when object is not found?

Question

I am searching for a blank cell in a table. Want to have a msg or run a command when there is no blank cell. I tried below versions but none of them worked

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

and also this one

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

score 0 · Answer 1 · answered May 06 '19 at 10:44

No need for selects and selection, you could try:

Sub try()

On Error GoTo noblanks
  MsgBox ActiveSheet.ListObjects("Tabel1").DataBodyRange.SpecialCells(xlCellTypeBlanks).Count & " blank cells are found"
  Exit Sub
noblanks:
MsgBox "no blank cell is found"

End Sub

score 0 · Accepted Answer · answered May 06 '19 at 11:21

I suggest to catch the error and check if BlankCells is 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

You might benefit from reading …

How to proceed when object is not found?

2 Answers2