Page 214 - Excel Progamming Weekend Crash Course

k540629 ch15.qxd 9/2/03 9:34 AM Page 189

Session 15 — Find and Replace Operations 189

Listing 15-1 Program to find and select data in a range

Public Sub FindDataInRange(r As Range, target As Variant)

‘ Searches for target in a range r. If found, selects
‘ the cell. If not, displays a message.

Dim r1 As Range
Set r1 = r.Find(target)
If r1 Is Nothing Then
MsgBox target & “ was not found.”
Else
r1.Select
End If

End Sub
Public Sub TestFind()

‘ Tests the FindDataInRange procedure.
FindDataInRange ActiveSheet.Range(“A1:G10”), “hello”

End Sub

The Find method does not locate formula results. Thus, if a cell contains a
formula that evaluates to the value 6, searching for “6” does not match that
Note cell.

The FindNext and FindPrevious Methods
After you call the Find method, you may want to look for other occurrences of the target
within the range, and that’s just what the FindNext and FindPrevious methods do. They
are identical except that FindNext searches forward and FindPrevious searches backward.
The syntax is:

FindNext(After)
FindPrevious(After)
The after argument is optional. It is a Range object specifying the single cell where the
search is to commence. Typically you pass the range that was returned by the Find method
or the previous call to FindNext or FindPrevious. This way the search continues from
where it left off. If After is omitted, the search starts back at the first cell in the range
being searched. Both methods return a Range object referencing the cell where a match
was found or Nothing if no match is found.