| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if first character of string is in array
Hello.
The code below works correctly except I need it to determine if a string begins with another string stored in an array, not contains the string stored in the array. For example If the data is Should return Actually Returns 112 112 112 201 115 201 115 183 115 183 183 254 390 here is the code that I have modified so far from another posting here. Dim rng As Range, cell As Range, arrVar As Variant Dim strAddress1 As String, rngUnion As Range Set rng = Range("Main!c:c") For Each arrVar In Array("1", "2") Set cell = rng.Find(arrVar, LookIn:=xlValues, Lookat:=xlPart, MatchCase:=True) If Not cell Is Nothing Then strAddress1 = cell.Address Do If rngUnion Is Nothing Then Set rngUnion = cell Else Set rngUnion = Union(cell, rngUnion) End If Set cell = rng.FindNext(cell) Loop While Not cell Is Nothing And _ cell.Address < strAddress1 End If Next arrVar If Not rngUnion Is Nothing Then rngUnion.EntireRow.Copy End If Any help would be appreciated. Thanks Jonathan |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if first character of string is in array
It should return
112 starts with 1 201 starts with 2 115 starts with 1 183 starts with 1 254 starts with 2 Maybe you should make it clearer what your criteria is. Regards, Tom Ogilvy Jonathan wrote in message news:Dskhb.1101$XS4.677@edtnps84... Hello. The code below works correctly except I need it to determine if a string begins with another string stored in an array, not contains the string stored in the array. For example If the data is Should return Actually Returns 112 112 112 201 115 201 115 183 115 183 183 254 390 here is the code that I have modified so far from another posting here. Dim rng As Range, cell As Range, arrVar As Variant Dim strAddress1 As String, rngUnion As Range Set rng = Range("Main!c:c") For Each arrVar In Array("1", "2") Set cell = rng.Find(arrVar, LookIn:=xlValues, Lookat:=xlPart, MatchCase:=True) If Not cell Is Nothing Then strAddress1 = cell.Address Do If rngUnion Is Nothing Then Set rngUnion = cell Else Set rngUnion = Union(cell, rngUnion) End If Set cell = rng.FindNext(cell) Loop While Not cell Is Nothing And _ cell.Address < strAddress1 End If Next arrVar If Not rngUnion Is Nothing Then rngUnion.EntireRow.Copy End If Any help would be appreciated. Thanks Jonathan |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if first character of string is in array
You are correct, the output should be what you listed. For posting purposes
I was planning on only putting one value in the array. Sorry about the confusion. Jonathan "Tom Ogilvy" wrote in message ... It should return 112 starts with 1 201 starts with 2 115 starts with 1 183 starts with 1 254 starts with 2 Maybe you should make it clearer what your criteria is. Regards, Tom Ogilvy Jonathan wrote in message news:Dskhb.1101$XS4.677@edtnps84... Hello. The code below works correctly except I need it to determine if a string begins with another string stored in an array, not contains the string stored in the array. For example If the data is Should return Actually Returns 112 112 112 201 115 201 115 183 115 183 183 254 390 here is the code that I have modified so far from another posting here. Dim rng As Range, cell As Range, arrVar As Variant Dim strAddress1 As String, rngUnion As Range Set rng = Range("Main!c:c") For Each arrVar In Array("1", "2") Set cell = rng.Find(arrVar, LookIn:=xlValues, Lookat:=xlPart, MatchCase:=True) If Not cell Is Nothing Then strAddress1 = cell.Address Do If rngUnion Is Nothing Then Set rngUnion = cell Else Set rngUnion = Union(cell, rngUnion) End If Set cell = rng.FindNext(cell) Loop While Not cell Is Nothing And _ cell.Address < strAddress1 End If Next arrVar If Not rngUnion Is Nothing Then rngUnion.EntireRow.Copy End If Any help would be appreciated. Thanks Jonathan |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if first character of string is in array
I would use:
Set cell = rng.Find(arrVar & "*", LookIn:=xlValues, _ Lookat:=xlWhole, MatchCase:=True) Include a wildcard on the end of the search term. Look at xlWhole This has worked for me in similar situations. -- Regards, Tom Ogilvy "Jonathan" wrote in message news:UwAhb.8713$XS4.7645@edtnps84... You are correct, the output should be what you listed. For posting purposes I was planning on only putting one value in the array. Sorry about the confusion. Jonathan "Tom Ogilvy" wrote in message ... It should return 112 starts with 1 201 starts with 2 115 starts with 1 183 starts with 1 254 starts with 2 Maybe you should make it clearer what your criteria is. Regards, Tom Ogilvy Jonathan wrote in message news:Dskhb.1101$XS4.677@edtnps84... Hello. The code below works correctly except I need it to determine if a string begins with another string stored in an array, not contains the string stored in the array. For example If the data is Should return Actually Returns 112 112 112 201 115 201 115 183 115 183 183 254 390 here is the code that I have modified so far from another posting here. Dim rng As Range, cell As Range, arrVar As Variant Dim strAddress1 As String, rngUnion As Range Set rng = Range("Main!c:c") For Each arrVar In Array("1", "2") Set cell = rng.Find(arrVar, LookIn:=xlValues, Lookat:=xlPart, MatchCase:=True) If Not cell Is Nothing Then strAddress1 = cell.Address Do If rngUnion Is Nothing Then Set rngUnion = cell Else Set rngUnion = Union(cell, rngUnion) End If Set cell = rng.FindNext(cell) Loop While Not cell Is Nothing And _ cell.Address < strAddress1 End If Next arrVar If Not rngUnion Is Nothing Then rngUnion.EntireRow.Copy End If Any help would be appreciated. Thanks Jonathan |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if first character of string is in array
That worked perfectly.
Thanks Jonathan "Tom Ogilvy" wrote in message ... I would use: Set cell = rng.Find(arrVar & "*", LookIn:=xlValues, _ Lookat:=xlWhole, MatchCase:=True) Include a wildcard on the end of the search term. Look at xlWhole This has worked for me in similar situations. -- Regards, Tom Ogilvy "Jonathan" wrote in message news:UwAhb.8713$XS4.7645@edtnps84... You are correct, the output should be what you listed. For posting purposes I was planning on only putting one value in the array. Sorry about the confusion. Jonathan "Tom Ogilvy" wrote in message ... It should return 112 starts with 1 201 starts with 2 115 starts with 1 183 starts with 1 254 starts with 2 Maybe you should make it clearer what your criteria is. Regards, Tom Ogilvy Jonathan wrote in message news:Dskhb.1101$XS4.677@edtnps84... Hello. The code below works correctly except I need it to determine if a string begins with another string stored in an array, not contains the string stored in the array. For example If the data is Should return Actually Returns 112 112 112 201 115 201 115 183 115 183 183 254 390 here is the code that I have modified so far from another posting here. Dim rng As Range, cell As Range, arrVar As Variant Dim strAddress1 As String, rngUnion As Range Set rng = Range("Main!c:c") For Each arrVar In Array("1", "2") Set cell = rng.Find(arrVar, LookIn:=xlValues, Lookat:=xlPart, MatchCase:=True) If Not cell Is Nothing Then strAddress1 = cell.Address Do If rngUnion Is Nothing Then Set rngUnion = cell Else Set rngUnion = Union(cell, rngUnion) End If Set cell = rng.FindNext(cell) Loop While Not cell Is Nothing And _ cell.Address < strAddress1 End If Next arrVar If Not rngUnion Is Nothing Then rngUnion.EntireRow.Copy End If Any help would be appreciated. Thanks Jonathan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Check for character in string | Excel Discussion (Misc queries) | |||
| Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions | |||
| check if the text string start with a specific character | New Users to Excel | |||
| Check if a String is inside an Array | Excel Programming | |||
| Check if a String is inside an Array | Excel Programming | |||
![Jonathan[_8_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
