First, VBA has its own equivalent of application.search. You can read about
InStr in VBA's help.

Second, instead of parsing the string yourself, how about using VBA's Split?
(It does require xl2k or higher, though.)

Option Explicit
Sub testme()
Dim myStr As String
Dim mySplit As Variant
Dim myVal As String
Dim iCtr As Long

'populate it someway
myStr = "cat, bat"

mySplit = Split(myStr, ",")

For iCtr = LBound(mySplit) To UBound(mySplit)
'remove any leading/trailing spaces
myVal = Trim(mySplit(iCtr))
MsgBox myVal
Next iCtr

End Sub

tracktraining wrote:

Hi All,

Can anyone tell me why the code will not go thru the loop the second time
again?
so I enter "Bat, Cat" in the textbox and all the complaints for Bat get copy
over correctly. But the code stop at If LCase(Worksheets("Complaint
Log").Cells(datecompRng.Row, DescripRng.Column).Value) Like "*" &
LCase(wordkey) & "*" Then when it comes to Cat (meaning the msgbox
"contain word" doesn't display when it should). So, is there a reason why it
is not doing the comparsion the second time around?

thanks for the help!

See code below:

Do Until IsNumeric(Application.Search(",", MyValue)) = False
If IsNumeric(Application.Search(",", MyValue)) Then
Num_Values = Num_Values + 1
MyValue = Right(MyValue, Len(MyValue) -
Application.Search(",", MyValue))
End If
Loop

MyValue = keyphrase
ReDim NewValue(Num_Values)

For i = 0 To UBound(NewValue)
If IsNumeric(Application.Search(",", MyValue)) Then
NewValue(i) = Left(MyValue, Application.Search(",", MyValue)
- 1)
MyValue = Right(MyValue, Len(MyValue) -
Application.Search(",", MyValue))
Else
NewValue(i) = MyValue
End If
wordkey = NewValue(i)

With Worksheets("complaint Log")
Set datecompRng = .Range("I2", .Cells(.Rows.Count,
"I").End(xlUp))
Set DescripRng = .Range("E2", .Cells(.Rows.Count,
"E").End(xlUp))
End With

For Each datecompRng In datecompRng.Cells
If datecompRng = date1 And datecompRng <= date2 Then
MsgBox "something here"
If LCase(Worksheets("Complaint
Log").Cells(datecompRng.Row, DescripRng.Column).Value) Like "*" &
LCase(wordkey) & "*" Then
MsgBox "contain word"
datecompRng.EntireRow.Copy
Sheets("Result_Sheet").Select
Cells(Rows.Count, 1).End(xlUp)(2).Select
Selection.PasteSpecial Paste:=xlAll
End If

End If
Next datecompRng
Call product_math(wordkey)
Next i
--
Learning

--

Dave Peterson