Don,
You seem to want to obfuscate the sound practice of specify values for
persistent parameters when using the FIND command. Is your intent to
confuse the OP, discredit the advice or are you suggesting to always close
and reopen excel whenever using code that contains the FIND command to avoid
having to specify the parameter values/never run macros unless excel has just
been opened.

In your specific instance, the defaults provided the desired results.

Not if the string is produced by a formula. there was no discussion which
said whether the strings were constants or produced by formulas. That is
another example of why it is best to specify.

--
Regards,
Tom Ogilvy




"Don Guillett" wrote:

I forgot to mention that when you leave Excel and come back the defaults, as
shown below in this recorded macro are restored. In your specific instance,
the defaults provided the desired results.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 7/7/2008 by Donald B. Guillett
'

'
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Tom is correct in that the other settings should be used if you are
changing often. See FIND for the rest of the story or record a macro using
edit find from the menu bar. I assumed you would not be using with many
variables so the defaults would suffice. The help file for findnext does
refer to FIND."Continues a search that was begun with the Find method".
Findnext is useful if you have a long column to look for a few hits.

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each
time you use this method. If you dont specify values for these arguments
the next time you call the method, the saved values are used. Setting
these arguments changes the settings in the Find dialog box, and changing
the settings in the Find dialog box changes the saved values that are used
if you omit the arguments. To avoid problems, set these arguments
explicitly each time you use this method.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom Ogilvy" wrote in message
...
Mark,

Since you mentiioned it, that is because Don's first code example assumes
that every cell in H1:H3 contains the word classification and that
len(c) - x
doesn't result in a negative value.

If you leave out half the necessary code, the remaining code often looks
elegant.

In his second example
Set c = .Find("Classification", LookIn:=xlValues)

this line looks elegant as well, but leaves out most of the settings
which
will insure that it works consistently (even if it is predominantly the
example code from the VBA FINDNEXT help example - even Microsoft was
sloppy
there).

--
Regards,
Tom Ogilvy


"Mark Stephens" wrote:

Hi Mike,

Thanks very much for that works like a dream, regards,

Mark

PS Don your code is very similar but slightly more elgant looking but
unfortunately I got an error : Runtime error 5 Invalid Procedure call or
argument




"Mike H" wrote in message
...
Mark,

Try this

Sub sonic()
Dim MyRange As Range
Set MyRange = Range("A2:AA2")
For Each c In MyRange
If InStr(1, c, "Classification") = 1 Then
mystring = Mid(c, 15, Len(c))
'do something with MyString
End If
Next
End Sub

Mike

"Mark Stephens" wrote:

Hi,

I have 3 cells containing string values:

Classification_Asset Class
Classification_Risk Classification
Classification_Geographical Breakdown

I want to tell the code to look in each cell along a row until it
finds
the
string value: Classification_

Whsn it find it i then want it to pass the rest of the string to
another
variable, something like this:

Dim sFirstStringValue As String
Dim sSecondStringValue As String


sFirstStringValue = "Classification_"
sSecondStringValue = Everything thatcomesafterfiststringvalue

Help much appreciated,

Regards, Mark