Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter 'From' 'To' Dates in 1 Col
I have one column of dates, say, in the range D5:D11. My header is at D4.
In F2 I have a variable "FromDate" and in F3 a variable "ToDate". I would like to be able to enter a 'From' and 'To' date in cells F2 & F3 respectively. Then have a VBA program grab those 2 dates and filter this single column of dates by saying date range is greater than or equal to F2 AND less than or equal to F3. Below is the VBA code I have attempted with no success. You help would be appreciated. Thanks, John Sub FilterDates() Dim FromDate As Date Dim ToDate As Date FromDate = Range ("F2") 'Location on Worksheet where the FromDate will be entered. ToDate = Range ("F3") 'Location on Worksheet where the ToDate will be entered. FromDate = Worksheets("Sheet1").AutoFilter.Range("D5:D11") Range("D4").Select 'This is the header cell above the single column of dates. Selection.AutoFilter With Worksheets("Sheet1") If .AutoFilterMode Then With .AutoFilter.Filters(1) If .On Then FromDate = .Criteria1 End With End If End With 'Selection.AutoFilter With Worksheets("Sheet1") If .AutoFilterMode Then With .AutoFilter.Filters(1) If .On Then ToDate = .Criteria2 End With End If End With Selection.AutoFilter field:=1, Criteria1:="=FromDate", Operator:=xlAnd _ , Criteria2:="<=ToDate" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter 'From' 'To' Dates in 1 Col
you can execute step by step to find which step is wrong and what does
exactly each one... Here are my ideas: FromDate = Worksheets("Sheet1").AutoFilter.Range("D5:D11") why "FromDate = ..." ? don't you already have fromdate set ? what was the wanted purpose of this line ? (forget the line) Range("D4").Select do not need to select it, forget it too Selection.AutoFilter -- this is not great because you cut it if already on ! With Worksheets("Sheet1") If not .AutoFilterMode Then .range("D4").autofilter end if 'this one replace the old end if . Now you are sure it's on at this step and if you think a filter can be somewhere else: dim a$ a="$D$4" If .AutoFilterMode Then 'it's already on if .autofilter.range.address < a then ' but on the wrong range .autofiltermode = off 'cut the old one off .range(a).autofilter end if else ' it was off .range(a).autofilter 'activate it if not end if With .AutoFilter.Filters(1) If .On Then FromDate = .Criteria1 it's already on ! forget that test and you wish the opposite: .criteria1 = fromdate (I think you do not really take care of what you are doing) Whatever, forget all the lines from this precedent one included until this last one (you keep it): Selection.AutoFilter field:=1, Criteria1:="=FromDate", Operator:=xlAnd _ , Criteria2:="<=ToDate" which was the only usefull but is not correct: Selection.AutoFilter field:=1, Criteria1:="<=" & FromDate, Operator:=xlAnd _ , Criteria2:="<=" &ToDate end with end sub first try this and remember to 1- exeute step by step to see what really does each step 2- do not write ramdom code: read the visual basic help to know how works the properties you use |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter 'From' 'To' Dates in 1 Col
Sub FilterDates()
Dim FromDate As Date Dim ToDate As Date Dim rngToFilter As Range With Worksheets("Sheet1") FromDate = .Range("F2").Value2 ToDate = .Range("F3").Value2 Set rngToFilter = .Range("D4:D11") End With rngToFilter.AutoFilter _ Field:=1, _ Criteria1:="=" & FromDate, _ Operator:=xlAnd, _ Criteria2:="<=" & ToDate End Sub -- Dianne Butterworth John Childs wrote: I have one column of dates, say, in the range D5:D11. My header is at D4. In F2 I have a variable "FromDate" and in F3 a variable "ToDate". I would like to be able to enter a 'From' and 'To' date in cells F2 & F3 respectively. Then have a VBA program grab those 2 dates and filter this single column of dates by saying date range is greater than or equal to F2 AND less than or equal to F3. Below is the VBA code I have attempted with no success. You help would be appreciated. Thanks, John Sub FilterDates() Dim FromDate As Date Dim ToDate As Date FromDate = Range ("F2") 'Location on Worksheet where the FromDate will be entered. ToDate = Range ("F3") 'Location on Worksheet where the ToDate will be entered. FromDate = Worksheets("Sheet1").AutoFilter.Range("D5:D11") Range("D4").Select 'This is the header cell above the single column of dates. Selection.AutoFilter With Worksheets("Sheet1") If .AutoFilterMode Then With .AutoFilter.Filters(1) If .On Then FromDate = .Criteria1 End With End If End With 'Selection.AutoFilter With Worksheets("Sheet1") If .AutoFilterMode Then With .AutoFilter.Filters(1) If .On Then ToDate = .Criteria2 End With End If End With Selection.AutoFilter field:=1, Criteria1:="=FromDate", Operator:=xlAnd _ , Criteria2:="<=ToDate" End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter 'From' 'To' Dates in 1 Col
You're welcome.
-- Dianne Butterworth John Childs wrote: Diane, it worked!! A brilliant and elegant solution. Thanks, John "Dianne Butterworth" wrote: Sub FilterDates() Dim FromDate As Date Dim ToDate As Date Dim rngToFilter As Range With Worksheets("Sheet1") FromDate = .Range("F2").Value2 ToDate = .Range("F3").Value2 Set rngToFilter = .Range("D4:D11") End With rngToFilter.AutoFilter _ Field:=1, _ Criteria1:="=" & FromDate, _ Operator:=xlAnd, _ Criteria2:="<=" & ToDate End Sub -- Dianne Butterworth John Childs wrote: I have one column of dates, say, in the range D5:D11. My header is at D4. In F2 I have a variable "FromDate" and in F3 a variable "ToDate". I would like to be able to enter a 'From' and 'To' date in cells F2 & F3 respectively. Then have a VBA program grab those 2 dates and filter this single column of dates by saying date range is greater than or equal to F2 AND less than or equal to F3. Below is the VBA code I have attempted with no success. You help would be appreciated. Thanks, John Sub FilterDates() Dim FromDate As Date Dim ToDate As Date FromDate = Range ("F2") 'Location on Worksheet where the FromDate will be entered. ToDate = Range ("F3") 'Location on Worksheet where the ToDate will be entered. FromDate = Worksheets("Sheet1").AutoFilter.Range("D5:D11") Range("D4").Select 'This is the header cell above the single column of dates. Selection.AutoFilter With Worksheets("Sheet1") If .AutoFilterMode Then With .AutoFilter.Filters(1) If .On Then FromDate = .Criteria1 End With End If End With 'Selection.AutoFilter With Worksheets("Sheet1") If .AutoFilterMode Then With .AutoFilter.Filters(1) If .On Then ToDate = .Criteria2 End With End If End With Selection.AutoFilter field:=1, Criteria1:="=FromDate", Operator:=xlAnd _ , Criteria2:="<=ToDate" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter on Dates | Excel Discussion (Misc queries) | |||
Filter Dates? | Excel Discussion (Misc queries) | |||
How to filter out all the partial dates from a big list of dates? | Excel Discussion (Misc queries) | |||
Filter dates by the day | Excel Discussion (Misc queries) | |||
how to filter dates? | New Users to Excel |