Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why won't it work when I use VBA to set autofilter criteria to ce.
Below is an example of what I had programmed for a custom autofilter. The
cells in the criteria contain dates. When I run the Macro it hides all the data. However if I then go into the custom filter manually the dates are already entered in the criteria and I just have to press OK for the filter to work. Is there a way around having to do this manually or can excel not handle this sort of functionality? Dim sStartDate As String Dim sEndDate As String Dim sBottomRow As String sStartDate = Sheets("Data").Cells(6, 2).Value sEndDate = Sheets("Data").Cells(6, 4).Value sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row) Selection.AutoFilter Field:=8, Criteria1:="=" & sStartDate, Operator:= _ xlAnd, Criteria2:="<=" & sEndDate |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why won't it work when I use VBA to set autofilter criteria to ce.
Dim sStartDate As Date
Dim sEndDate As Date Dim sBottomRow As String sStartDate = Sheets("Data").Cells(6, 2).Value sEndDate = Sheets("Data").Cells(6, 4).Value sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row) Selection.AutoFilter Field:=8, _ Criteria1:="=" & sStartDate, _ Operator:=xlAnd, _ Criteria2:="<=" & sEndDate if that doesn't work modify the above to Selection.AutoFilter Field:=8, _ Criteria1:="=" & clng(sStartDate), _ Operator:=xlAnd, _ Criteria2:="<=" & clng(sEndDate) -- Regards, Tom Ogilvy "biggy" wrote in message ... Below is an example of what I had programmed for a custom autofilter. The cells in the criteria contain dates. When I run the Macro it hides all the data. However if I then go into the custom filter manually the dates are already entered in the criteria and I just have to press OK for the filter to work. Is there a way around having to do this manually or can excel not handle this sort of functionality? Dim sStartDate As String Dim sEndDate As String Dim sBottomRow As String sStartDate = Sheets("Data").Cells(6, 2).Value sEndDate = Sheets("Data").Cells(6, 4).Value sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row) Selection.AutoFilter Field:=8, Criteria1:="=" & sStartDate, Operator:= _ xlAnd, Criteria2:="<=" & sEndDate |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why won't it work when I use VBA to set autofilter criteria to
Sorry if I didn't explain myself properly. The clng funtion prevents the
macro from working all together. Excel seems to recognise the date formats properly but simply doesn't show the data even though it is filtering on the correct values. It does show the data if in macro you actually have the actual values in the data i.e. 12/03/03. But for some reason just won't show the data when you use cell references rather than actual values even though those cells contain the same values. So think I might give up on this one. "Tom Ogilvy" wrote: Dim sStartDate As Date Dim sEndDate As Date Dim sBottomRow As String sStartDate = Sheets("Data").Cells(6, 2).Value sEndDate = Sheets("Data").Cells(6, 4).Value sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row) Selection.AutoFilter Field:=8, _ Criteria1:="=" & sStartDate, _ Operator:=xlAnd, _ Criteria2:="<=" & sEndDate if that doesn't work modify the above to Selection.AutoFilter Field:=8, _ Criteria1:="=" & clng(sStartDate), _ Operator:=xlAnd, _ Criteria2:="<=" & clng(sEndDate) -- Regards, Tom Ogilvy "biggy" wrote in message ... Below is an example of what I had programmed for a custom autofilter. The cells in the criteria contain dates. When I run the Macro it hides all the data. However if I then go into the custom filter manually the dates are already entered in the criteria and I just have to press OK for the filter to work. Is there a way around having to do this manually or can excel not handle this sort of functionality? Dim sStartDate As String Dim sEndDate As String Dim sBottomRow As String sStartDate = Sheets("Data").Cells(6, 2).Value sEndDate = Sheets("Data").Cells(6, 4).Value sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row) Selection.AutoFilter Field:=8, Criteria1:="=" & sStartDate, Operator:= _ xlAnd, Criteria2:="<=" & sEndDate |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why won't it work when I use VBA to set autofilter criteria to
I understand you completely!! but don't have the answer!
In the example I'm trying I just want to filter a list of dates that are equal to or less than todays date. (i.e. Fields with a date of either today or in the past) I have the code: Selection.AutoFilter Field:=1, Criteria1:="<=" & Date to assign the filter. This shows correctly in the custom filter dialog after the code is run, but no data is shown. To make the data show all I need to do is go into the custom dialog and click OK (without changing anything!!). When OK is clicked the data shows as expected. I've been round three people at work but knowone can answer this!! Weird eh???? "biggy" wrote: Sorry if I didn't explain myself properly. The clng funtion prevents the macro from working all together. Excel seems to recognise the date formats properly but simply doesn't show the data even though it is filtering on the correct values. It does show the data if in macro you actually have the actual values in the data i.e. 12/03/03. But for some reason just won't show the data when you use cell references rather than actual values even though those cells contain the same values. So think I might give up on this one. "Tom Ogilvy" wrote: Dim sStartDate As Date Dim sEndDate As Date Dim sBottomRow As String sStartDate = Sheets("Data").Cells(6, 2).Value sEndDate = Sheets("Data").Cells(6, 4).Value sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row) Selection.AutoFilter Field:=8, _ Criteria1:="=" & sStartDate, _ Operator:=xlAnd, _ Criteria2:="<=" & sEndDate if that doesn't work modify the above to Selection.AutoFilter Field:=8, _ Criteria1:="=" & clng(sStartDate), _ Operator:=xlAnd, _ Criteria2:="<=" & clng(sEndDate) -- Regards, Tom Ogilvy "biggy" wrote in message ... Below is an example of what I had programmed for a custom autofilter. The cells in the criteria contain dates. When I run the Macro it hides all the data. However if I then go into the custom filter manually the dates are already entered in the criteria and I just have to press OK for the filter to work. Is there a way around having to do this manually or can excel not handle this sort of functionality? Dim sStartDate As String Dim sEndDate As String Dim sBottomRow As String sStartDate = Sheets("Data").Cells(6, 2).Value sEndDate = Sheets("Data").Cells(6, 4).Value sBottomRow = CStr(Sheets("Data").Range("B8").End(xlDown).Row) Selection.AutoFilter Field:=8, Criteria1:="=" & sStartDate, Operator:= _ xlAnd, Criteria2:="<=" & sEndDate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter doesn't work | Excel Discussion (Misc queries) | |||
Autofilter(Top 10...): What does it do? How does it work? | Excel Discussion (Misc queries) | |||
AutoFilter Criteria VBA | Excel Programming | |||
VBA Autofilter Criteria | Excel Programming | |||
Help: Autofilter doesn't work | Excel Programming |