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

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

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

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

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

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

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
>

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