PDA

View Full Version : Solved: Selection.Find Error handling



simora
04-24-2013, 06:21 PM
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.


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 "



Any ideas ?

Thanks

mikerickson
04-24-2013, 11:09 PM
When the search term is not present, .Find does not error, instead, it returns Nothing.

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

snb
04-25-2013, 12:45 AM
Use Excel's built-in functionality:

Sub M_snb()
on error resume next
y=selection.specialcells(4).count
if err.number <>0 then msgbox "No empty cells"
end sub

simora
04-25-2013, 07:36 PM
Thanks guys:
I didn't know that it DID NOT error.