Thanks a lot Rik, Ican have my cake and eating it now:)


"Rick Rothstein (MVP - VB)" wrote in
message ...
Try it this way...

Set rTopRow = Sheets("FundList").Range("1:1")

Rick


"Mark Stephens" wrote in message
...
Hi Mike,

Yes I tend to agree with both actually, your code works and I can
understand it so it serves its purpose well for me. On the other side of
the coin, it means I have to specify the size of my search area (I tried
substituting the reference to the range:

Set rTopRow = Sheets("FundList").Range("C1:AA1")

with

Set rTopRow = Sheets("FundList").Row(1)

but it doesn't work.

If I could do this then it would save me having to define the no of
columns in the top row to search but then even if i set it to the maximum
in future (Range("C1:XFD1") it has the same effect so no worries.


FindNext would be more efficient I guess in that it will just search
until the last one if found but I guess it depends what is your purpose
for doing it.... to get it working in a reasonable manner without being
too slow or to write the most elegant code possible!

I will save the latter for my retirement, at the moment I am grateful for
your assistance with the former.

Thanks again everyone, regards, Mark



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

Having missed the development of this thread while travelling home I'm
simply amazed at the size and diversity of it. On the theme of KISS and
returning to the original request made by the OP what is wrong with the
original suggestion posted by myself?

I'm sure this now invites me to be shot down in flames but in defence of
that solution it variously:-

Provides an answer the OP recognised as solving the problem.

Was intuative in what it was doing so probably helped in his
understanding.

Clearly wasn't elegant but I don't understand what the OP meant by this
except the lack of elegance was made in a comparison to a solution that
didn't work and was error prone.

Will not produce errors in the event of empty or cells not containing
the
search string.

Doesn't give rise to the seemingly complicated issues of using
FIND/FINDNEXT
which even MVP's seem to find difficult to agree on.

Is only 7 lines long (I agree it should be a couple more if variables
had
been correctly dimensioned)

Executes as fast as any other offered solution (This test was limited to
searching ~ 5000 cells and sending the results to the immediate window.

Hasn't sent the OP to bed with a headache:)


So for the education of myself why does KISS not apply?

Regards,

Mike


"Don Guillett" wrote:


I guess I'm just of the old KISS school. I tend to try to listen to
what the
ORIGINAL poster said "STRING VALUE" and solve THAT problem. Sometimes,
OPs
can become confused if it gets too fancy. However, you are correct..
I'm
surprised you didn't also berate me for not using dim as I should.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom Ogilvy" wrote in message
...
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 don't 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