loop issue

T

tracktraining

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
 
D

Dave Peterson

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
 
T

tracktraining

thanks Dave, your code made it easier for me to follow and think thru the
next couple of steps!
--
Learning


tracktraining said:
Hi Dave,

I will test that out and get back to you. thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top