Check if first character of string is in array

  • Thread starter Thread starter Jonathan
  • Start date Start date
J

Jonathan

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
 
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
 
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
 
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.
 
That worked perfectly.

Thanks

Jonathan

Tom Ogilvy said:
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.
Click to expand...
 
Back
Top