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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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: Replacing SaveSetting with saving to a parameter file (Access)
    ... overkill for app settings, ... Private Sub Form_Unload ... Private Sub SetKey(ByVal Key As String, ... Dim RS As DAO.Recordset ...
    (microsoft.public.vb.database.dao)
  • Re: System Dates
    ... Dim MM As String ... same regardless of the users international settings, ... You can use the Format function with the named format "short date" to ...
    (microsoft.public.excel.programming)
  • Ability to place all the documents being exported into a .ZIP file
    ... Dim boo1 As Boolean ... ' Dim strDistillerSecurity As String ... ' ' See if we can find the Acrobat Distiller ... ' ' Save off the current Distiller security settings ...
    (microsoft.public.access.modulesdaovba)
  • Re: retrieve date from string
    ... CDate uses the localility settings of the system. ... Public Function ConvDate(DateIn As String) As String ... Dim stDate As String ... Dim dDate As Date ...
    (microsoft.public.vb.general.discussion)
  • Re: hightlighting rows
    ... grabbed the settings in with the cut. ... > Roy apparently inadvertently got the SETTINGS code out of order on his ... >>> Dim UserFile As String, ...
    (microsoft.public.excel.programming)