Find Method

[Francis Hookam]

The Find Method in Visual Basic Help gives the example copied below - I
cannot work out how to use it for my needs which are:

Where X = a numeral from 1 to 9

Find X within (say) B2:B10
Find X within B2:J10
Find X within B2:C4

In this case I only need to know if the particular numeral exists in the
range although, for future use it would be good to be able to return the
cell reference

Can you help please?

Francis Hookham
________________________________________

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

 

[JE McGimpsey]

In this case I only need to know if the particular numeral exists in the
range although, for future use it would be good to be able to return the
cell reference

To just know whether the numeral exists:

Const X As Long = 2

With Range("B2:J10")
If Application.CountIf(.Cells, X) > 0 Then
MsgBox X & " exists in the range " & .Address(False, False)
Else
MsgBox X & " not found in range " & .Address(False, False)
End If
End With

If you want to find the first or only instance of X and return the
location:

Const X As Long = 2
Dim rFound As Range

With Range("B2:J10")
Set rFound = .Find( _
After:=.Item(.Count), _
What:=X, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rFound Is Nothing Then
MsgBox "Found " & X & " at " & rFound.Address(False, False)
Else
MsgBox X & " was not found in " & .Address(False, False)
End If
End With


Note: It's important to set all relevant arguments in the Find method -
otherwise the last values from a previous Find will be used. That can
cause incorrect (or at least confusing) results.

If you want to find every instance of X in the range, and return the
locations:


Const X As Long = 2
Dim rFound As Range
Dim sAddress As String
Dim sFoundAddress As String

With Range("B2:J10")
Set rFound = .Find( _
After:=.Item(.Count), _
What:=X, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rFound Is Nothing Then
sFoundAddress = rFound.Address
Do
sAddress = sAddress & ", " & rFound.Address(False, False)
Set rFound = .FindNext(after:=rFound)
Loop Until rFound.Address = sFoundAddress
MsgBox "Found " & X & " at " & Mid(sAddress, 3)
Else
MsgBox X & " was not found in " & .Address(False, False)
End If
End With

Looping until rFound.Address = sFoundAddress is necessary because
..FindNext will continue to loop through the worksheet.

MS's example is rather a stupid implementation. Using

Loop While Not c Is Nothing...

is only necessary if the loop changes the value in c (i.e., so that the
search term is not found on the .FindNext). But in that case

...And c.Address <> firstAddress

will always be False (if the value was changed, it can't be found!).
Likewise, if c.Value isn't changed within the loop,

...Not c Is Nothing...

will always be True (if it was found once, it must be found again!).