Help with the Find method

  • Thread starter Thread starter Mike NG
  • Start date Start date
M

Mike NG

Whenever I press F1 to get help on Find in XL2000, I am directed towards
Application.WorksheetFunction, but I want the
Worksheets("Sheet1").Range(..)
version

Any clues as to what's going on?


I want to know what row in "range" contains a value specified by the
search. I also need to know if the value doesn't exist. Values will be
unique. In one search the values will be sorted and in another search
they won't be. (I'd rather not do a While loop, and use the function
instead)
 
Mike,

Try something like the following:

Dim FoundCell As Range
Set FoundCell = Range("A1:A100").Find(what:="A",
after:=Range("A100"), _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If FoundCell Is Nothing Then
Debug.Print "Not Found"
Else
Debug.Print "Found at: " & FoundCell.Address
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Once you have help open, go to the answer wizard, type in find and hit the
search button. The second entry was the find method and selecting that
gives you what you want.

Find does not raise an error when the target string is not found. So you
can do

Dim rng as Range
set rng = Worksheets(1).Range("B9:Z26").Find(what:=sTarget, ... other args
.... )

if not rng is nothing then
msgbox "Found at " & rng.Address
else
msgbox "Not found"
End if

You can turn on the macro recorder and use Edit=>Find

This will give you a syntax example.

Note that the settings of the arguments are persistent - so if you don't set
them explicitly in your code, they are inherited from whatever their current
setting is. Sometimes this can cause unexpected behavior.

Find doesn't care about the order of the values. The example in help (in
xl97 and xl2000) for the find method shows how to search for multiple
occurences.
 
Once you have help open, go to the answer wizard, type in find and hit the
search button. The second entry was the find method and selecting that
gives you what you want.
Click to expand...
I've never noticed that tab before - many thanks
Find does not raise an error when the target string is not found. So you
can do

Dim rng as Range
set rng = Worksheets(1).Range("B9:Z26").Find(what:=sTarget, ... other args
... )

if not rng is nothing then
msgbox "Found at " & rng.Address
else
msgbox "Not found"
End if
Click to expand...
Cool - many thank to you and Chip for your answers
 
Back
Top