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

Look in the vba help index for
INSTR

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mark Stephens" wrote in message
...
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

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

Just realised you want to add the underscore to the search string and
eliminate iit from the 'found' string so substitute these 2 lines

If InStr(1, c, "Classification_") = 1 Then
mystring = Mid(c, 16, Len(c))

Mike

"Mike H" wrote:

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

or
Sub breakitup()
For Each c In Range("h1:h3")
x = InStr(c, "Classification") + 14
c.Offset(, 1) = Right(c, Len(c) - x)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

Hi,

The OP indicated and the examples given show that 'Classification' was at
the beginning of the string, your routine extracts the remaining text if the
search string appears anywhere in the cell. We will need the OP to clarify.

Mike

"Don Guillett" wrote:

or
Sub breakitup()
For Each c In Range("h1:h3")
x = InStr(c, "Classification") + 14
c.Offset(, 1) = Right(c, Len(c) - x)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

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

Guess so. Actually, if few and far between then FINDNEXT would be faster
than a loop.

Sub breakwithfindNEXT()
With Worksheets("sheet7").Range("h1:h50")
Set c = .Find("Classification", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
x = InStr(c, "Classification") + 14
c.Offset(, 1) = Right(c, Len(c) - x)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

The OP indicated and the examples given show that 'Classification' was at
the beginning of the string, your routine extracts the remaining text if
the
search string appears anywhere in the cell. We will need the OP to
clarify.

Mike

"Don Guillett" wrote:

or
Sub breakitup()
For Each c In Range("h1:h3")
x = InStr(c, "Classification") + 14
c.Offset(, 1) = Right(c, Len(c) - x)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

Here is a method that does not use a loop...

Dim FoundCell As Range
On Error Resume Next
Set FoundCell = Rows(5).Find("Classification_")
On Error GoTo 0
' Not sure what you want to do with it, so here I just show it to you
If Not FoundCell Is Nothing Then MsgBox Mid(FoundCell.Value, 16)

It assumes, for example purposes, that the row you want to search is 5,
although you can substitute a fixed range instead, something like
Range("E1:E100") for the Rows(5) reference, if required. The 16 in the last
statement is the length of "Classification_", the string you want to find.

Rick


"Mark Stephens" wrote in message
...
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

Add this line as the first line in the code or use the IF mentioned by Mike.
Or, as I said in the later post, if the "hits" are far apart use the
FINDNEXT macro instead.

On Error Resume Next


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mark Stephens" wrote in message
...
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

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

Hi Tom,

Thanks for the clarification, I am using:


Dim MyRange As Range

Dim c As Range

Dim sItemName As String





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




For Each c In MyRange


If InStr(1, c, "Classification_") = 1 Then



pbNoItems = pbNoItems + 1

sItemName = Mid(c, 16, Len(c))



Else: sItemName = ""




End If




Next


which works well, there are just 3 cells int he top row that aren't empty ad
they all start with Classification_

In fact the prefix is unecessary for the code really but it makes the whole
thing easier to understand and is worth the overhead of stripping it out, so
thanks everyone for your help and knowledge, kind regards, Mark


"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

Hi Rick,

I like this better since it saves me having to specify the length of the row
I want to search which makes it more scaleable and saves a bit of code
(counting the used range size to get the last entry in the row) but it only
returns the first instance how do I get it to cycle and find all the
instances of CLassification_ ?

Thanks and regards, Mark


"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is a method that does not use a loop...

Dim FoundCell As Range
On Error Resume Next
Set FoundCell = Rows(5).Find("Classification_")
On Error GoTo 0
' Not sure what you want to do with it, so here I just show it to you
If Not FoundCell Is Nothing Then MsgBox Mid(FoundCell.Value, 16)

It assumes, for example purposes, that the row you want to search is 5,
although you can substitute a fixed range instead, something like
Range("E1:E100") for the Rows(5) reference, if required. The 16 in the
last statement is the length of "Classification_", the string you want to
find.

Rick


"Mark Stephens" wrote in message
...
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

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

Well, of course, you will need a loop to do that and, of course, things get
messier than in the simple "find it once" code I posted earlier....

Dim Index As Long
Dim FoundCell As Range
Dim FirstAddress As String
Dim Answers() As String
ReDim Answers(1 To Columns.Count)
On Error Resume Next
With Worksheets("Sheet5").Rows(5)
Set FoundCell = .Find("Classification_")
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Index = Index + 1
Answers(Index) = Mid(FoundCell.Value, 16)
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing And FoundCell.Address <
FirstAddress
End If
End With
ReDim Preserve Answers(1 To Index)
On Error GoTo 0

The code above stores the values in an array named Answers. Here is a short
loop you can run after the code above to see all the results...

For Index = 1 To UBound(Answers)
Debug.Print Answers(Index)
Next

Rick


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

I like this better since it saves me having to specify the length of the
row I want to search which makes it more scaleable and saves a bit of code
(counting the used range size to get the last entry in the row) but it
only returns the first instance how do I get it to cycle and find all the
instances of CLassification_ ?

Thanks and regards, Mark


"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is a method that does not use a loop...

Dim FoundCell As Range
On Error Resume Next
Set FoundCell = Rows(5).Find("Classification_")
On Error GoTo 0
' Not sure what you want to do with it, so here I just show it to you
If Not FoundCell Is Nothing Then MsgBox Mid(FoundCell.Value, 16)

It assumes, for example purposes, that the row you want to search is 5,
although you can substitute a fixed range instead, something like
Range("E1:E100") for the Rows(5) reference, if required. The 16 in the
last statement is the length of "Classification_", the string you want to
find.

Rick


"Mark Stephens" wrote in message
...
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

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

Mark,

If you manually go into Edit=Find and search for Classification_ and set it
to "match entire cell contents"

then run the macro, it will find no instances for the reason I described
earlier. If you look at Don's code that uses find, it shows how to fine all
the instances (or look at the findnext code example in vba help). But
apparently this is all going over your head. (the lesson is, when using
FIND, declare the necessary values for at least the persistent arguments).

Don has pasted in that portion of the help for easy reference.

--
Regards,
Tom Ogilvy


"Mark Stephens" wrote:

Hi Rick,

I like this better since it saves me having to specify the length of the row
I want to search which makes it more scaleable and saves a bit of code
(counting the used range size to get the last entry in the row) but it only
returns the first instance how do I get it to cycle and find all the
instances of CLassification_ ?

Thanks and regards, Mark


"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is a method that does not use a loop...

Dim FoundCell As Range
On Error Resume Next
Set FoundCell = Rows(5).Find("Classification_")
On Error GoTo 0
' Not sure what you want to do with it, so here I just show it to you
If Not FoundCell Is Nothing Then MsgBox Mid(FoundCell.Value, 16)

It assumes, for example purposes, that the row you want to search is 5,
although you can substitute a fixed range instead, something like
Range("E1:E100") for the Rows(5) reference, if required. The 16 in the
last statement is the length of "Classification_", the string you want to
find.

Rick


"Mark Stephens" wrote in message
...
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

Hi Rick, yes it does get a lot more complex, thanks everyone for a wide
variety of answers and insughts here is the final code I am using which uses
most of the inout you kindly gave me:

Sub GetItemNameAndNoItems()

Dim rTopRow As Range
Dim rClassification As Range
Dim sClassificationName As String

'These are the outputs of this sub
Dim parrsItemname() As String
Dim pbNoItems As Byte


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

For Each rClassification In rTopRow

If InStr(1, rClassification, "Classification_") = 1 Then

pbNoItems = pbNoItems + 1
ReDim Preserve parrsItemname(pbNoItems)
sClassificationName = Mid(rClassification, 16,
Len(rClassification))
parrsItemname(pbNoItems) = sClassificationName


End If

Next rClassification


Rick, Don, Mike, Tom, thank you all for being so generous with your time and
knowledge (as always:), thanks and regards, Mark



"Rick Rothstein (MVP - VB)" wrote in
message ...
Well, of course, you will need a loop to do that and, of course, things
get messier than in the simple "find it once" code I posted earlier....

Dim Index As Long
Dim FoundCell As Range
Dim FirstAddress As String
Dim Answers() As String
ReDim Answers(1 To Columns.Count)
On Error Resume Next
With Worksheets("Sheet5").Rows(5)
Set FoundCell = .Find("Classification_")
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Index = Index + 1
Answers(Index) = Mid(FoundCell.Value, 16)
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing And FoundCell.Address <
FirstAddress
End If
End With
ReDim Preserve Answers(1 To Index)
On Error GoTo 0

The code above stores the values in an array named Answers. Here is a
short loop you can run after the code above to see all the results...

For Index = 1 To UBound(Answers)
Debug.Print Answers(Index)
Next

Rick


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

I like this better since it saves me having to specify the length of the
row I want to search which makes it more scaleable and saves a bit of
code (counting the used range size to get the last entry in the row) but
it only returns the first instance how do I get it to cycle and find all
the instances of CLassification_ ?

Thanks and regards, Mark


"Rick Rothstein (MVP - VB)" wrote
in message ...
Here is a method that does not use a loop...

Dim FoundCell As Range
On Error Resume Next
Set FoundCell = Rows(5).Find("Classification_")
On Error GoTo 0
' Not sure what you want to do with it, so here I just show it to you
If Not FoundCell Is Nothing Then MsgBox Mid(FoundCell.Value, 16)

It assumes, for example purposes, that the row you want to search is 5,
although you can substitute a fixed range instead, something like
Range("E1:E100") for the Rows(5) reference, if required. The 16 in the
last statement is the length of "Classification_", the string you want
to find.

Rick


"Mark Stephens" wrote in message
...
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

Thanks Tom,

I think find is the best solution, it's after 1am here in Singapore so I am
going to sleep on it and re-read Don's explanation (I did look at findnext
in help but found it a bit incomprehensible, maybe I will find it makes more
sense after some sleep).

Thanks and regards, Mark


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

If you manually go into Edit=Find and search for Classification_ and set
it
to "match entire cell contents"

then run the macro, it will find no instances for the reason I described
earlier. If you look at Don's code that uses find, it shows how to fine
all
the instances (or look at the findnext code example in vba help). But
apparently this is all going over your head. (the lesson is, when using
FIND, declare the necessary values for at least the persistent arguments).

Don has pasted in that portion of the help for easy reference.

--
Regards,
Tom Ogilvy


"Mark Stephens" wrote:

Hi Rick,

I like this better since it saves me having to specify the length of the
row
I want to search which makes it more scaleable and saves a bit of code
(counting the used range size to get the last entry in the row) but it
only
returns the first instance how do I get it to cycle and find all the
instances of CLassification_ ?

Thanks and regards, Mark


"Rick Rothstein (MVP - VB)" wrote
in
message ...
Here is a method that does not use a loop...

Dim FoundCell As Range
On Error Resume Next
Set FoundCell = Rows(5).Find("Classification_")
On Error GoTo 0
' Not sure what you want to do with it, so here I just show it to you
If Not FoundCell Is Nothing Then MsgBox Mid(FoundCell.Value, 16)

It assumes, for example purposes, that the row you want to search is 5,
although you can substitute a fixed range instead, something like
Range("E1:E100") for the Rows(5) reference, if required. The 16 in the
last statement is the length of "Classification_", the string you want
to
find.

Rick


"Mark Stephens" wrote in message
...
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

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

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 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

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 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

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

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

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