Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop issue
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop issue
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop issue
Hi Dave,
I will test that out and get back to you. thanks! -- Learning "Dave Peterson" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop issue
thanks Dave, your code made it easier for me to follow and think thru the
next couple of steps! -- Learning "tracktraining" wrote: Hi Dave, I will test that out and get back to you. thanks! -- Learning "Dave Peterson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
odd issue with a loop | Excel Programming | |||
odd issue with a loop | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Issue with Do Loop | Excel Programming |