Consulting

Results 1 to 4 of 4

Thread: Solved: Selection.Find Error handling

  1. #1
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    314
    Location

    Solved: Selection.Find Error handling

    I am selecting a range of cells in a column to find out if any are blank.
    If so, I post data in the blank cell, however, when the Selection.find fails,
    ( No cells in the selection is blank )
    I cant get it to go to another specific routine.

    This is the find code.

    [VBA]
    On Error GoTo Line22

    Selection.Find(What:="", After:=ActiveCell, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False).Select

    If ActiveCell.Value = "" Then ActiveCell = TextBox2.Value
    ActiveCell.AddComment
    End If
    Line22:

    Else

    MsgBox " No empty cells in range "
    [/VBA]


    Any ideas ?

    Thanks

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,761
    When the search term is not present, .Find does not error, instead, it returns Nothing.

    [VBA]dim FoundCell as Range

    Set FoundCell = Selection.Find(What:="", After:=ActiveCell, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False)

    If FoundCell Is Nothing Then
    MsgBox " No empty cells in range "
    Else
    With FoundCell
    .Value = TextBox2.Value
    .AddComment
    End With
    End If
    [/VBA]

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,378
    Use Excel's built-in functionality:
    [vba]
    Sub M_snb()
    on error resume next
    y=selection.specialcells(4).count
    if err.number <>0 then msgbox "No empty cells"
    end sub
    [/vba]

  4. #4
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    314
    Location
    Thanks guys:
    I didn't know that it DID NOT error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •