copy row if contain keyword
 

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

--
Learning

copy row if contain keyword
 

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" wrote:

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

--
Learning

copy row if contain keyword
 

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.
--
Learning


"JLGWhiz" wrote:

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" wrote:

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

--
Learning

copy row if contain keyword
 

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 wrote:

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

--
Learning

--

Dave Peterson

copy row if contain keyword
 

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!
--
Learning


"tracktraining" wrote:

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

--
Learning

copy row if contain keyword
 

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 wrote:

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!
--
Learning

"tracktraining" wrote:

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

--
Learning

--

Dave Peterson

copy row if contain keyword
 

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
--
Learning


"Dave Peterson" wrote:

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 wrote:

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!
--
Learning

"tracktraining" wrote:

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

--
Learning

--

Dave Peterson

copy row if contain keyword
 

Sorry about the typo (missing the dot).

But glad you got it working.

tracktraining wrote:

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
--
Learning

"Dave Peterson" wrote:

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 wrote:

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!
--
Learning

"tracktraining" wrote:

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

--
Learning

--

Dave Peterson


--

Dave Peterson