Re: Grabbing string values from the end of a string of an unknown

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
dguillett1@xxxxxxxxxxxxx
"Don Guillett" <dguillett1@xxxxxxxxxxxxx> wrote in message
news:eiLM38E4IHA.4272@xxxxxxxxxxxxxxxxxxxxxxx
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
dguillett1@xxxxxxxxxxxxx
"Tom Ogilvy" <TomOgilvy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9EB7149A-3EC3-40A3-BB90-8195B06CD5E5@xxxxxxxxxxxxxxxx
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" <MikeH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:85FD9B76-C826-4378-98D0-B6AA2DAFF479@xxxxxxxxxxxxxxxx
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









.

Relevant Pages

  • Re: Grabbing string values from the end of a string of an unknown
    ... 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: ... >> shown below in this recorded macro are restored. ... >> Microsoft MVP Excel ... >>> Tom is correct in that the other settings should be used if you are ...
    (microsoft.public.excel.programming)
  • Re: Grabbing string values from the end of a string of an unknown
    ... Microsoft MVP Excel ... having to specify the parameter values/never run macros unless excel has just ... shown below in this recorded macro are restored. ... > The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved> each ...
    (microsoft.public.excel.programming)
  • Re: Grabbing string values from the end of a string of an unknown
    ... "Don Guillett" wrote: ... ORIGINAL poster said "STRING VALUE" and solve THAT problem. ... shown below in this recorded macro are restored. ... The settings for LookIn, LookAt, SearchOrder, and MatchByte are ...
    (microsoft.public.excel.programming)
  • Re: Grabbing string values from the end of a string of an unknown
    ... "Don Guillett" wrote: ... ORIGINAL poster said "STRING VALUE" and solve THAT problem. ... shown below in this recorded macro are restored. ... The settings for LookIn, LookAt, SearchOrder, and MatchByte are ...
    (microsoft.public.excel.programming)
  • Re: Grabbing string values from the end of a string of an unknown
    ... I forgot to mention that when you leave Excel and come back the defaults, as shown below in this recorded macro are restored. ... Microsoft MVP Excel ... 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. ... >> Whsn it find it i then want it to pass the rest of the string to>> another ...
    (microsoft.public.excel.programming)