|
Grabbing string values from the end of a string of an unknown length
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 |
Grabbing string values from the end of a string of an unknown length
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 |
Grabbing string values from the end of a string of an unknown leng
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 |
Grabbing string values from the end of a string of an unknown
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 |
Grabbing string values from the end of a string of an unknown
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 |
Grabbing string values from the end of a string of an unknown leng
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 |
Grabbing string values from the end of a string of an unknown
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 |
Grabbing string values from the end of a string of an unknown length
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 |
Grabbing string values from the end of a string of an unknown leng
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 |
Grabbing string values from the end of a string of an unknown
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 |
Grabbing string values from the end of a string of an unknown
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 |
Grabbing string values from the end of a string of an unknown length
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 |
Grabbing string values from the end of a string of an unknown
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 |
Grabbing string values from the end of a string of an unknown length
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 |
Grabbing string values from the end of a string of an unknown
I forgot to mention that when you leave Excel and come back the defaults, as
shown below in this recorded macro are restored. 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 |
Grabbing string values from the end of a string of an unknown
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 |
Grabbing string values from the end of a string of an unknown length
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 |
Grabbing string values from the end of a string of an unknown
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 |
Grabbing string values from the end of a string of an unknown
Don,
You seem to want to obfuscate the sound practice of specify values for persistent parameters when using the FIND command. Is your intent to confuse the OP, discredit the advice or are you suggesting to always close and reopen excel whenever using code that contains the FIND command to avoid having to specify the parameter values/never run macros unless excel has just been opened. In your specific instance, the defaults provided the desired results. Not if the string is produced by a formula. there was no discussion which said whether the strings were constants or produced by formulas. That is another example of why it is best to specify. -- Regards, Tom Ogilvy "Don Guillett" wrote: I forgot to mention that when you leave Excel and come back the defaults, as shown below in this recorded macro are restored. In your specific instance, the defaults provided the desired results. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 7/7/2008 by Donald B. Guillett ' ' Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "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 |
Grabbing string values from the end of a string of an unknown
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 |
Grabbing string values from the end of a string of an unknown
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 |
Grabbing string values from the end of a string of an unknown
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 |
Grabbing string values from the end of a string of an unknown
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 |
Grabbing string values from the end of a string of an unknown
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 |
| All times are GMT +1. The time now is 06:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com