copy row if contain keyword

[tracktraining]

Hi Everyone,

i am pretty sure this is an easy fix but i can't seem to figure it out. I
am trying to write the following: find the row that the date fall within
certain date and then within that row, look at cell column "E" and find a
keyword, then both conditions are met then copy the entire row into
sheets("Paste_results").

I am unable to get the "find the rows that the date fall within certain
dates" but I can't get the second part. I can't get it though the second if.
My code is below: (please help)

date1 = Me.StartDate.Value
date2 = Me.EndDate.Value
keyword = Me.Product.Value

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

Sheets("paste_results").Cells.Clear

For Each datecompRng In datecompRng.Cells
If datecompRng >= date1 And datecompRng <= date2 Then
MsgBox "date between"
If Cells(datecompRng, "E").Value = "*keyword*" Then
MsgBox "contain word"
datecompRng.EntireRow.Copy
Sheets("paste_results").Select
Cells(Rows.Count, 1).End(xlUp)(2).Select
Selection.PasteSpecial Paste:=xlAll
'copy the row
'paste the row in sheet reports
End If
End If
Next datecompRng


thank you,
tracktraining

 

[JLGWhiz]

I did not test this but I think the modifications should allow it to do what
you want

date1 = Me.StartDate.Value
date2 = Me.EndDate.Value
keyword = Me.Product.Value

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

Sheets("paste_results").Cells.Clear

For Each dt In datecompRng.Cells
If dt >= date1 And dt <= date2 Then
MsgBox "date between"
If Cells(dt.Row, "E").Value = "*keyword*" Then
MsgBox "contain word"
dt.EntireRow.Copy
Sheets("paste_results").Select
Cells(Rows.Count, 1).End(xlUp)(2).Select
Selection.PasteSpecial Paste:=xlAll
'copy the row
'paste the row in sheet reports
End If
End If
Next datecompRng

 

[tracktraining]

your change didn't work.

The keyword is going to be a word or two words (whatever the user decide to
input). And I want to compare the string inside the column E cells to the
keyword. If any of the words inside the cell matches with the keyword(s),
then I want to copy that entire row.

thanks.

 

[Dave Peterson]

If Cells(datecompRng, "E").Value = "*keyword*" Then
could be:

If Cells(datecompRng, "E").Value like "*" & keyword & "*" Then
or
If lcase(Cells(datecompRng, "E").Value) like "*" & lcase(keyword) & "*" Then

or
If instr(1, Cells(datecompRng, "E").Value, keyword, vbtextcompare) > 0 Then

 

[tracktraining]

I tried all of your methods and it still doesn't work. If I have nothing in
the Me.product field, then it goes thru the loop. But when I do have
something in the field (i.e. POD), then it doesn't go thru the loop.

please help. thank you!

 

[Dave Peterson]

The first thing I would do is to qualify all the ranges.

Like:
Cells(datecompRng, "E").Value
becomes:
Worksheets("Complaint Log").Cells(datecompRng, "E").Value

Then the next thing I would do is to eliminate some tests to see if I could get
it to work.

'put something in x999 that should work
For Each datecompRng In Worksheets("Complaint Log").range("X999").cells
'drop this check
'If datecompRng >= date1 And datecompRng <= date2 Then
MsgBox "date between"
If lcase(Worksheets("Complaint Log")Cells(datecompRng, "E").Value)
_
= like "*" & lcase(keyword) & "*" Then
MsgBox "contains word"
End If
'End If
Next datecompRng

To see if that works.

 

[tracktraining]

Hi Dave,

thanks for helping out. With your hints, the code finally works.

this is what i had to do:

If LCase(Worksheets("Complaint Log").Cells(datecompRng.Row,
DescripRng.Column).Value) Like "*" & LCase(keyword) & "*" Then


thanks again.

- Tracktraining

 

[Dave Peterson]

Sorry about the typo (missing the dot).

But glad you got it working.

Hi Dave,

thanks for helping out. With your hints, the code finally works.

this is what i had to do:

If LCase(Worksheets("Complaint Log").Cells(datecompRng.Row,
DescripRng.Column).Value) Like "*" & LCase(keyword) & "*" Then

thanks again.

- Tracktraining