Page 215 - Excel Progamming Weekend Crash Course

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

190 Saturday Afternoon

Be aware that the various Find methods do not remember which cells they have already
searched. By default, they — if called repeatedly — just loop back and search the range
again and again. The trick to making sure the range is searched only once is to keep track
of the cell where the first match is found, using the Range.Address property. The address
of subsequent matches is then compared to this, and it is clear when the methods have
completed searching the range and have looped back to the beginning.
The function CountStringsInRange in Listing 15-2 illustrates this technique. This func-
tion is passed a range and a target string as its arguments. It counts the number of times
the target is found within the range and returns the result to the caller. This listing also
contains a procedure, TestCountStrings, that you can run to try out the function.

Listing 15-2 A function to count the number of times a string appears in a range

Public Function CountStringsInRange(r As Range, target As String) As Long
‘ Counts the number of times the string target is
‘ found in a range, and returns the result.
Dim count As Long
Dim r1 As Range
Dim AddressOfFirstFind

count = 0
‘Find the first instance.
Set r1 = r.Find(target)
‘ If the string is not found, return 0.
If r1 Is Nothing Then
CountStringsInRange = 0
Exit Function
‘ If found, loop to find any other instances.
End If

‘ Save the address of the first location.
AddressOfFirstFind = r1.Address
Do
‘ Increment the total.
count = count + 1
‘ Find the next instance starting at the
‘ previous find location.
Set r1 = r.FindNext(r1)
‘ Loop until the process returns to the first location.
Loop While r1.Address <> AddressOfFirstFind

‘ Return the result.