Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search multiple columns
I was try to get the rows that matches below criteria, to sheet 'Search'
'Test' in column F of Sheet 'Data' and 'Final' in column J of Sheet 'Data' and date less than 3 months which should be taken from column I Sheet 'Data' Can anyone help me on this please..? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search multiple columns
Use AdvancedFilter
http://www.ozgrid.com/Excel/advanced-filter.htm -- Regards Dave Hawley www.ozgrid.com "Kash" wrote in message ... I was try to get the rows that matches below criteria, to sheet 'Search' 'Test' in column F of Sheet 'Data' and 'Final' in column J of Sheet 'Data' and date less than 3 months which should be taken from column I Sheet 'Data' Can anyone help me on this please..? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search multiple columns
Hi Dave, I need to do this using macros as its should match all the 3
criterias, then copy the entire row to a different sheet (Search) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search multiple columns
Hi Kash
Try the below macro. When you say 'date less then 3 months' I assume the dates from 1st Feb'10 to current date will be considered when you run this today...IF not adjust that part to suit... Sub Macro() Dim ws1 As Worksheet, ws2 As Worksheet Dim varFound As Variant, varSearch As Variant Dim strAddress As String, lngLastRow As Long Set ws1 = Sheets("Data") Set ws2 = Sheets("Search") varSearch = "Test" With ws1.Range("F1:F" & ws1.Cells(Rows.Count, "F").End(xlUp).Row) Set varFound = .Find(varSearch, LookIn:=xlValues, LookAt:=xlWhole) If Not varFound Is Nothing Then strAddress = varFound.Address Do If ws1.Range("J" & varFound.Row) = "Final" And _ DateDiff("m", ws1.Range("I" & varFound.Row), Date) < 3 And _ ws1.Range("I" & varFound.Row) <= Date Then lngLastRow = ws2.Cells(Rows.Count, "F").End(xlUp).Row + 1 ws1.Rows(varFound.Row).Copy ws2.Rows(lngLastRow) End If Set varFound = .FindNext(varFound) Loop While Not varFound Is Nothing And _ varFound.Address < strAddress End If End With End Sub -- Jacob (MVP - Excel) "Kash" wrote: I was try to get the rows that matches below criteria, to sheet 'Search' 'Test' in column F of Sheet 'Data' and 'Final' in column J of Sheet 'Data' and date less than 3 months which should be taken from column I Sheet 'Data' Can anyone help me on this please..? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum on multiple columns with search criteria | Excel Worksheet Functions | |||
search multiple columns | Excel Discussion (Misc queries) | |||
Search Across Multiple Columns | Excel Discussion (Misc queries) | |||
search cells in multiple columns of same row | Excel Discussion (Misc queries) | |||
LOOKUP w/ multiple search columns? | Excel Discussion (Misc queries) |