Display message if "find" command comes up blank ??

[vrzimmerm]

At one point within a macro I'm searching a column for a specific text
using the "Find" command. If the search comes up empty I want to
display a message to the user and end the macro. How do I do this?

 

[JW]

One way:
Sub likeThis()
Dim found As Variant
Set found = Columns(1).Find(What:="gfdfg", _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If Not found Is Nothing Then
MsgBox "Found it"
Else
MsgBox "Didn't find it!"
End If
End Sub

 

[Guest]

How about something like this:

Sub ReportFindResults()

' Define search range
Dim myRange As Range
Set myRange = Range("a:a")

' Define value to search for
Dim ValueToFind As String
ValueToFind = "something"

With myRange
Dim c As Range
Set c = .Find(ValueToFind, LookIn:=xlValues)
If Not c Is Nothing Then
' do something
MsgBox "I found " & ValueToFind & "!"
Else
MsgBox ValueToFind & " was not found"
Exit Sub
End If
End With
End Sub


HTH,
Matthew Pfluger

 

[JE McGimpsey]

One way:

Dim rFound As Range
Set rFound = Columns(1).Find("mytext")
If rFound Is Nothing Then
MsgBox "Didn't find 'mytext'"
Else
'your macro stuff here
End if

 

[vrzimmerm]

One way:
Sub likeThis()
Dim found As Variant
Set found = Columns(1).Find(What:="gfdfg", _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If Not found Is Nothing Then
MsgBox "Found it"
Else
MsgBox "Didn't find it!"
End If
End Sub

Thanks, but I'm not sure how to fit this into my macro. What I'm
trying to do is activate the cell containing the first occurrence of
the text I'm searching for, and then delete that row and all rows
below it.

 

[vrzimmerm]

One way:

Dim rFound As Range
Set rFound = Columns(1).Find("mytext")
If rFound Is Nothing Then
MsgBox "Didn't find 'mytext'"
Else
'your macro stuff here
End if

Great stuff.....worked perfectly. Many thanks.