Search multiple columns

  • Thread starter Thread starter Kash
  • Start date Start date
K

Kash

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..?
 
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)
 
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
 
Back
Top