Help with the Find method
 

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

Help with the Find method
 

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


"Mike NG" wrote in message
...
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

Help with the Find method
 

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.

--
Regards,
Tom Ogilvy






Mike NG wrote in message
...
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

Help with the Find method
 

On Sat, 2 Aug 2003 at 11:11:14, Tom Ogilvy (Tom Ogilvy
) wrote:
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.

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

Cool - many thank to you and Chip for your answers
--
Mike