using the Find method in a VBA macro

[Mudd]

I have got a working example of using the Find method in a VBA macro to get
the cell row of the value being searched for, but it only works if the value
being searched for exists in the specified range. If the value being
searched for is not in the range, then a runtime error occurs.
My test contains a worksheet with the numbers 1 to 12 in cells A8 to A19.

Here is my macro;
Sub findRow()
' Worksheet contains the numbers 1 to 12 in cells A8 to A19.

Dim lngMaxRow As Long
Dim Row

Range("A9").Select
Selection.End(xlDown).Select
lngMaxRow = ActiveCell.Row

Range("A8:A" & lngMaxRow).Select

'Look for 4
Row = Selection.Find(what:="4", LookIn:=xlValues, LookAt:=xlWhole).Activate
If Row <> "" Then
MsgBox "Found at row " & ActiveCell.Row
End If

'Look for 15
Row = Selection.Find(what:="15", LookIn:=xlValues, LookAt:=xlWhole).Activate
If Row <> "" Then
MsgBox "Found at row " & ActiveCell.Row
End If

End Sub

The Find method works fine for the search of "4" whcih it finds at row 11,
but instead of simply not finding the value "15", it causes a runtime error
"Object variable or With block variable not set".

How can I use the Find method so that this error doesn't occur just because
it can't find that particular value?
I was hoping it would return zero or false or something similar to indicate
that the value wasn't found.

 

[Norman Jones]

Hi Mudd,

Try something like:

Sub findRow()
Dim rng As Range, rng1 As Range
Dim searchStr As Variant
Dim msg As String

searchStr = "4"

With ActiveWorkbook.Worksheets("Sheet1")
Set rng = .Range(Range("A9"), Range("A9").End(xlDown))
End With

Set rng1 = rng.Find(searchStr, LookIn:=xlValues, _
LookAt:=xlWhole)
If Not rng1 Is Nothing Then
msg = "The search value """ & searchStr _
& """ found at " & rng1.Address
Else
msg = "The search value """ & searchStr & """ not found"
End If
MsgBox msg, vbInformation, "Search Result"
End Sub