-
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
-
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]
-
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]
-
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
-
Forum Rules