jabodaddy


I have working code to autofilter the list I'm interested in for a single criterion in several fields. However, I can't find anything anywhere that shows how to write the object model fields so that I can use a date range.

ie. I thought that I would be able to use

RangeObject.AutoFilter(Field:=1, Criteria1 > Date1, Operator:=xlAnd, Criteria2 < Date2)

I'm not sure that it's possible, but maybe someone knows a workaround.





Re: Using Autofilter in VBA with a range of criteria

jabodaddy


I just thought of something that I should add to clarify my request for help here.

I'll give the snippet of code to help:

week = InputBox("Enter the desired Week Number.", "Date Query")

Selection.AutoFilter
Selection.AutoFilter 8, "REBALE"
Selection.AutoFilter Field:=9, Criteria1:="28"
Selection.AutoFilter 13, week

This currently works for sorting my data by the Week Number column. However, I want to have two seperate input boxes that give me the capability to use 2 different string criteria while using > or < operators. I know that you could simply put the week numbers in the following:

.AutoFilter (Field, Criteria1:=">=3", xlAnd, Criteria2:="<=6")

but I want it to be interactive, if possible.

I hope that's a bit more clear than mud!

Thanks.

JS







Re: Using Autofilter in VBA with a range of criteria

jabodaddy

Well, I believe that I finally found an answer by trying to concatenate strings to make the thing work.

It might not be the slickest way, but I thought I would post it since it works and someone else could have a similar question.

startweek = InputBox("Enter the desired Week Number.", "Start Week")
week1 = ">=" & startweek

endweek = InputBox("Enter the desired Week Number.", "End Week")
week2 = "<=" & endweek

'
' User to input the needed defect codes
'
dcode = InputBox("Enter the code in question.", "Defect Code")
'
' User to input the status
'
status = InputBox("Enter the status in question. (Note that it must be either REBALE, REJECT, or HELD.)", "Bale Status")


Workbooks("Production Database Master.xls").Activate

Application.ScreenUpdating = False

Sheets("Prod").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.Count - 1 ' Count the rows and
' subtract the header.

Selection.AutoFilter
Selection.AutoFilter 8, status
Selection.AutoFilter 9, dcode
If week2 = "<=" Then
Selection.AutoFilter 13, startweek
Else
Selection.AutoFilter 13, week1, xlAnd, week2
End If
matched_criteria = 0 ' Set variable to zero.
check_row = 0 ' Set variable to zero.
While Not IsEmpty(ActiveCell) ' Check to see if row
' height is zero.
ActiveCell.Offset(1, 0).Select
If ActiveCell.RowHeight = 0 Then
check_row = check_row + 1
Else
matched_criteria = matched_criteria + 1
End If
Wend

If row_count = check_row Then ' If these are equal,
' nothing was returned.
MsgBox "No matching data were found.", vbExclamation
Sheets("Summary").Range("F21").Value = 0
Else
MsgBox "The total count for these criteria is " & matched_criteria - 1
' Display the number of records returned
End If

Sheets("Summary").Range("F15").Value = startweek
Sheets("Summary").Range("F16").Value = endweek
Sheets("Summary").Range("F18").Value = status
Sheets("Summary").Range("E21").Value = dcode

Sheets("Summary").Range("F21").Value = _
matched_criteria - 1


Range("A:M").AutoFilter
Sheets("Summary").Range("F21").Select


Application.ScreenUpdating = True

/js







Re: Using Autofilter in VBA with a range of criteria

Joe Dawson

Thanks for posting the code. I am going to toy with it at work tomorrow. The concept is interesting.