How can I use variables as my autofilter criteria ??? 1

[PHibbert]

I need to be able to use variables as my criteria for an autofilter which I run through VBA. I know how to use the xlAnd operator to create a custom autofilter based on 2 static values e.g.:

Selection.AutoFilter Field:=1, Criteria1:=&quot;>=5&quot;, Operator:=xlAnd, 'Criteria2:=&quot;<=10&quot;

Gives me everything in Col.1 from 5 - 10 inclusive.

However, I want to be able to filter based on two dates which the user will have entered earlier in the program.

I want to do something like:

Selection.AutoFilter Field:=4, Criteria1:=>=StartDate, Operator:=xlAnd, Criteria2:=<=EndDate
(where StartDate and EndDate are the variables)

I have also tried using the xlBetween operator to get around the problem but don't think that's right either!:

Selection.AutoFilter Field:=3, Criteria1:=StartDate, Operator:=xlBetween, Criteria2:=EndDate

And even tried this (I was getting pretty desperate!), but I don't think it was ever going to work!

Selection.AutoFilter Field:=4, Criteria1:=StartDate, Operator:=xlGreaterEqual, Operator:=xlAnd Criteria2:=EndDate, Operator:=xlLessEqual

 

[RobCPA]

The problem with dates is that the autofilter dropdown box converts the dates to string, so to get your code to work, you'll also need to convert your date criteria to string. Also, the way you see the date in the combo box is exactly the way the string needs to look. For example, if the combo box shows the date as 01/01/02, then that is exactly how your criteria will need to be formatted. Dates such as 1/1/02 and 01/01/2002 will not work.

I've created a sub that shows the date being set, then it converted to string, and finally setting the autofilter with the string parameters.

Sub myauto()
Dim DateStart As Date
Dim DateEnd As Date
Dim sDateStart As Variant
Dim sDateEnd As String

DateStart = #1/1/02#
DateEnd = #1/5/02#

sDateStart = &quot;0&quot; & Month(DateStart) & &quot;/0&quot; & Day(DateStart) & &quot;/&quot; & Right(Year(DateStart), 2)
sDateEnd = &quot;0&quot; & Month(DateEnd) & &quot;/0&quot; & Day(DateEnd) & &quot;/&quot; & Right(Year(DateEnd), 2)

Selection.AutoFilter Field:=1, Criteria1:=&quot;>=&quot; & sDateStart, Operator:=xlAnd, Criteria2:=&quot;<=&quot; & sDateEnd

End Sub


You probably need to write some code that will handle the conversion of any dates, not just ones that have 1 digit for the month or the day.

That was a really good question.
Rob

 

[DaleWatson123321]

PHibbert,

Quite honestly, I &quot;shudder&quot; every time I see the &quot;great lengths&quot; Excel users go to in order to perform &quot;filtering&quot; operations such as those described above.

With all due respect, I'm totally convinced that once users appreciate how &quot;easy&quot; it is to set up &quot;criteria&quot; on a SEPARATE sheet, they will begin to use this method, and be TOTALLY SOLD on it.

A little &quot;background info&quot; on this... Microsoft has provided relatively little help on this subject, and furthermore, has failed to eliminate a couple of related BUGS.

One BUG informs the user that it's NOT possible to place criteria on a SEPARATE sheet. NOT True. It IS possible. And not only is it possible, it is &quot;preferable&quot;. Placing criteria on the SAME sheet as the database (which Microsoft says is required) causes CONSIDERABLE problems - and both frustrates users and prevents them from creating dynamic and complex criteria.

When I speak of &quot;complex&quot;, it's not complex to create the criteria, but complex in terms of the ability to isolate data in the database based on elaborate criteria when required. Examples of criteria that could ALL be part of a &quot;compound&quot; condition are: by Year, Month, Group, Region, City, Department, Employee, Sales, Expenditures, etc.

The other BUG says it's NOT possible to extract records to a SEPARATE sheet. This is &quot;true&quot; IF one attempts to use the Menu method (Data - Filter - Advanced Filter). Ironically, it is the ERROR message itself that &quot;stops you in your tracks&quot;. Using VBA, there is NO problem.

By placing the criteria on a SEPARATE sheet, it enables one to create VERY complex formulas - VERY easily. And these criteria can be used interactively with VBA and user-input.

Another &quot;odd&quot; situation that has probably caused some Excel users to get &quot;frightened off&quot; from creating criteria, is when creating a formula for the criteria. When the formula references one of the field names in the database, the formula will return #NAME?. In earlier versions of Excel, I can't recall ever seeing an explanation for the #NAME?, but in later versions like Excel 2000, there is an explanatin to be found IF you look hard enough. It says the #NAME? is &quot;normal&quot; when used as part of a criteria.

Criteria can easily be created to glean whatever data is necessary from the database. The criteria can also reference common cells in the workbook. For example, it is common practice for me to set up applications where all the user has to do to convert a workbook to a &quot;new year&quot;, is to simply enter one number in one cell - the number of course being the year. The formulas in the criteria then are &quot;automatically&quot; set for the new year.

If a database has data for more than one year, all the user has to do, again, is to enter just the year, and the criteria will automatically reference that different year.

There is the option to: 1) filter-in-place, or 2) to extract the records to a SEPARATE sheet (using VBA). If a user wants to isolate the data in either of these ways, it can be made as simple as entering the number of the month in a cell and then clicking a macro-button.

Keep in mind, too, that in addition to being able to filter records in place or to extract them, it's also possible to use the SAME criteria to create &quot;summary&quot; reports using Excel's database &quot;formulas&quot; - e.g. =DSUM, =DCOUNTA, =DMAX, =DMIN =DAVERAGE, etc.

One last point on the use of the database formulas and filtering... This option is CONSIDERABLY FASTER than &quot;looping through records&quot; using VBA. The reason is because the database functions are &quot;custom&quot; functions and as such are executed using the &quot;C&quot; programming language - a MUCH faster language than VBA.

I hope this helps you (and other readers) in better understanding and appreciating the SIGNIFICANT power of Excel's &quot;database functionality&quot;.

If you'd like help with the particular task at hand, I'd be pleased to help out. The best approach would be for you to email me your file. I'll then modify and return it. The result will serve as a solution, and an example that will likely cause you to have a MUCH better appreciation and understanding of this component of Excel. If you happen to have sensitive data in your file, perhaps you could replace it with fictitious data that still reflects the type of data you're working with.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca