i1 = " week1, week2, week5, week16, week18, week24 "

i2 = " week16 "

If InStr( i1, i2 ) 0 Then

When I put above code in a loop to check all rows in my sheet not only
" week16 " responds but also " week1 " comes in.

How can I avoid "week1" ?
Or " week2 " while looping for " week24" ?

Any idea ? Thank you very much.

On May 13, 2:27*pm, " wrote:
i1 = *" week1, week2, week5, week16, week18, week24 "

i2 = *" week16 "

If InStr( i1, i2 ) 0 Then

When I put above code in a loop to check all rows in my sheet not only
" week16 " responds but also " week1 " comes in.

How can I avoid "week1" ?
Or " week2 " while looping for " week24" ?

Any idea ? Thank you very much.

Oppps !

i1 = " week5, week16, week18, week24 "

Hi,

With the spaces removed this nor correctly returns 19 for me

i1 = "week1,week2,week5,week16,week18,week24"
i2 = "week16"
If InStr(i1, i2) Then
x = InStr(i1, i2)
MsgBox x
End If

Mike

" wrote:

i1 = " week1, week2, week5, week16, week18, week24 "

i2 = " week16 "

If InStr( i1, i2 ) 0 Then

When I put above code in a loop to check all rows in my sheet not only
" week16 " responds but also " week1 " comes in.

How can I avoid "week1" ?
Or " week2 " while looping for " week24" ?

Any idea ? Thank you very much.

Thank you very much Mike.
What i meant was:

week 16, week 26, week 30 is the string of a multiple selection.
So my purpose is to sum the sales of weeks 16,26 and 30.
I use this "..If InStr( i1, i2 ) 0 Then.." and week 1 joins the
club.

Thank you.

On May 13, 9:24*am, " wrote:
Thank you very much Mike.
What i meant was:

week 16, week 26, week 30 is the string of a multiple selection.
So my purpose is to sum the sales of weeks 16,26 and 30.
I use this *"..If InStr( i1, i2 ) 0 Then.." and week 1 joins the
club.

Thank you.

Rumkus,

You could either add another integer digit to your "week" name for
single digits, e.g. week05 instead of week5, and use StrComp (see
"TestStrComp" below), or you could parse off the number and do a
number comparison (see "TestParse" below and read the commented
assumption).

Best,

Matthew Herbert

Sub TestStrComp()
Dim strOne As String
Dim strTwo As String
Dim varSplit As Variant
Dim intI As Integer

strOne = " week05, week16, week18, week24 "
strTwo = " week16 "

varSplit = Split(strOne, ",")

For intI = LBound(varSplit) To UBound(varSplit)
If StrComp(Trim(strTwo), Trim(varSplit(intI)), vbTextCompare) = -1
Then
Debug.Print "strTwo:"; strTwo; " | strOne:"; varSplit(intI)
End If
Next
End Sub

Sub TestParse()
Dim strOne As String
Dim strTwo As String
Dim intOne As Integer
Dim intTwo As Integer
Dim strCnst As String
Dim varSplit As Variant
Dim intI As Integer

'Assumes ALL strings start with the "week" text,
' and contain "week" text
strCnst = "week"
strOne = " week5, week16, week18, week24 "
strTwo = " week16 "

strTwo = Trim(strTwo)
intTwo = CInt(Replace(strTwo, strCnst, ""))
varSplit = Split(strOne, ",")

For intI = LBound(varSplit) To UBound(varSplit)
strOne = Trim(varSplit(intI))
intOne = CInt(Replace(strOne, strCnst, ""))

If intOne intTwo Then
Debug.Print "strTwo:"; strTwo; " | strOne:"; varSplit(intI)
End If
Next

End Sub

Sub TestStrComp() does it all !!

Thank you very much Matt for your time.
I really appreciated.

Kindest regards