Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not filtering by date
i've tried the code below but it does not seems to filter out the
recent 2 weeks entries. Kinda stumped here and will appreciate any help. Thanks! Private Sub filterp_Click() Dim Class As String Dim FilterRange As Range Dim myDate As Date Class = ComboBox1.Value myDate = Format(Date - 14, "dd/mm/yy") Range("A1").Select Selection.AutoFilter Set FilterRange = ActiveSheet.Range("A1:AK" & _ ActiveSheet.Range("A65536").End(xlUp).Row) FilterRange.AutoFilter Field:=1, Criteria1:=Class, Operator:=xlAnd, _ Field:=9, Criteria1:="=" & myDate End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not filtering by date
You use the operator:=xlAnd to have a joint criteria on a single
column, not to use filter criteria on two different columns. So replace your filterrange code with with FilterRange ..AutoFilter Field:=1, Criteria1:=Class, ..AutoFilter Field:=9, Criteria1:="=" & cdbl(myDate) end with Peter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not filtering by date
=) thanks for the enlightment, no wonder the code won't work at all.
now it's perfect. thanks peter & dave |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not filtering by date
sorry to bother you guys again but now after i applied this code to
another filter option(with partial search criteria), an error appeared and said " AutoFilter method of Range class failed". What happened? Private Sub search_click() Dim FindWhat As String Dim FilterRange As Range Dim myDate As Date myDate = Format(Date - 14, "dd/mm/yyyy") FindWhat = InputBox If FindWhat = "" Then Exit Sub Dim LR As Long LR = Cells(Rows.Count, 1).End(xlUp).Row Set FilterRange = Range("A1:A" & LR) Application.ScreenUpdating = False ActiveSheet.AutoFilterMode = False FilterRange.AutoFilter Field:=1, Criteria1:="*" & FindWhat & "*" FilterRange.AutoFilter Field:=9, Criteria1:="=" & CDbl(myDate)-------Error occurs here If FilterRange.SpecialCells(12).Count = 1 Then ActiveSheet.AutoFilterMode = False Set FilterRange = Nothing Application.ScreenUpdating = True MsgBox FindWhat & " was not found.", 64, "No Matching Part Names." Else Set FilterRange = Nothing Application.ScreenUpdating = True End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not filtering by date
Looks to me as if your FilterRange ( = Range("A1:A" & LR)) only
selects column A, so your Field:=9 selects a column which is not in the FilterRange. Peter |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not filtering by date
ah..icic. oki, got that done.
just a question though(learning vb now =P). From my understanding, field=9 tells the filter which column to filter right? But will this Range("A1").Select or this Set FilterRange = ActiveSheet.Range("A1:AK" & _ ActiveSheet.Range("A65536").End(xlUp).Row) affects the value of the field? Meaning that if the range select is B1 or B1:BK, field = 9 (which was column I) will now become column k? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not filtering by date
No. Column J comes after I. The first column of the range selected to be
filtered, is always Field1. If columns("K:Z") are selected, then column K is Field1. Column Z is Field16. Field17 and up do not exist in this range. Mike F "chOcO" wrote in message ups.com... ah..icic. oki, got that done. just a question though(learning vb now =P). From my understanding, field=9 tells the filter which column to filter right? But will this Range("A1").Select or this Set FilterRange = ActiveSheet.Range("A1:AK" & _ ActiveSheet.Range("A65536").End(xlUp).Row) affects the value of the field? Meaning that if the range select is B1 or B1:BK, field = 9 (which was column I) will now become column k? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not filtering by date
ah..icic. Thanks mike!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering by a date | Excel Discussion (Misc queries) | |||
Filtering by newest date | Excel Discussion (Misc queries) | |||
Need help filtering a date column | Excel Discussion (Misc queries) | |||
Help filtering date related data | Excel Worksheet Functions | |||
Filtering Names By Date | Excel Programming |