Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
most popular keyword | Excel Worksheet Functions | |||
Keyword | Excel Programming | |||
keyword search | Excel Discussion (Misc queries) | |||
Sharing with Keyword | Excel Discussion (Misc queries) | |||
SQL Keyword | Excel Programming |