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.
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 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!
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
row_count = Selection.Rows.Count - 1 ' Count the rows and
' subtract the header.
Selection.AutoFilter 8, status
Selection.AutoFilter 9, dcode
If week2 = "<=" Then
Selection.AutoFilter 13, startweek
Selection.AutoFilter 13, week1, xlAnd, week2
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.
If ActiveCell.RowHeight = 0 Then
check_row = check_row + 1
matched_criteria = matched_criteria + 1
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
MsgBox "The total count for these criteria is " & matched_criteria - 1
' Display the number of records returned
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
Application.ScreenUpdating = True