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

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

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

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

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

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,

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

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

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

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

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