Prev: Combo Box question
Next: Combination to Two formulas with the Help of AND / OR / IF Functions
From: Kash on 26 Apr 2010 23:26
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..?
From: ozgrid.com on 26 Apr 2010 23:38
Use AdvancedFilter
http://www.ozgrid.com/Excel/advanced-filter.htm


--
Regards
Dave Hawley
www.ozgrid.com
"Kash" <Kash(a)discussions.microsoft.com> wrote in message
news:9ED7D321-BBA4-4FF0-A13D-E258F2336CCD(a)microsoft.com...
>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..?

From: Kash on 27 Apr 2010 00:16
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)
From: Jacob Skaria on 27 Apr 2010 00:46
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..?
 | 
Pages: 1
Prev: Combo Box question
Next: Combination to Two formulas with the Help of AND / OR / IF Functions