I have a spreadsheet (XL 2007) that contains about 16,000 records. I
need the ability to extract records based upon anywhere from 1 to 3
fields and print each record. The fields are selected from a userform
that contains 3 combo boxes – combo1 contains Supervisor’s names,
combo2 contains Employee’s, and combo3 contains a work location. What
is the best way to locate the records based upon the criteria selected
by the User?
Thanks.
James

Have you considered using the Range.AdvancedFilter method
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)




"John Smith"
wrote in message
...
I have a spreadsheet (XL 2007) that contains about 16,000 records. I
need the ability to extract records based upon anywhere from 1 to 3
fields and print each record. The fields are selected from a userform
that contains 3 combo boxes – combo1 contains Supervisor’s names,
combo2 contains Employee’s, and combo3 contains a work location. What
is the best way to locate the records based upon the criteria selected
by the User?
Thanks.
James

On Jan 4, 10:00*pm, "Jim Cone" wrote:
Have you considered using the Range.AdvancedFilter method
'---
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: *compares, matches, counts, lists, finds, deletes....)

"John Smith"
wrote in ...
I have a spreadsheet (XL 2007) that contains about 16,000 records. I
need the ability to extract records based upon anywhere from 1 to 3
fields and print each record. The fields are selected from a userform
that contains 3 combo boxes – combo1 contains Supervisor’s names,
combo2 contains Employee’s, and combo3 contains a work location. What
is the best way to locate the records based upon the criteria selected
by the User?
Thanks.
James

Thanks Jim, I will look into that option. Is this something that I can
attach to a command button to trigger it?
James

You already have a userform, so use the OK/Continue button on it.
When the button is clicked, use the values entered into the combo boxes as the filter criteria and
filter the list.
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Greatest Film List.xls - in the free folder)



"John Smith"
wrote in message
...
On Jan 4, 10:00 pm, "Jim Cone" wrote:
Have you considered using the Range.AdvancedFilter method
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)



"John Smith"
wrote in messagenews:
...
I have a spreadsheet (XL 2007) that contains about 16,000 records. I
need the ability to extract records based upon anywhere from 1 to 3
fields and print each record. The fields are selected from a userform
that contains 3 combo boxes – combo1 contains Supervisor’s names,
combo2 contains Employee’s, and combo3 contains a work location. What
is the best way to locate the records based upon the criteria selected
by the User?
Thanks.
James

Thanks Jim, I will look into that option. Is this something that I can
attach to a command button to trigger it?
James

On Jan 5, 10:26*am, "Jim Cone" wrote:
You already have a userform, so use the OK/Continue button on it.
When the button is clicked, use the values entered into the combo boxes as the filter criteria and
filter the list.
'---
Jim Cone
Portland, Oregon USA *.http://www.mediafire.com/PrimitiveSoftware*.
(Greatest Film List.xls - in the free folder)

"John Smith"
wrote in ...
On Jan 4, 10:00 pm, "Jim Cone" wrote:





Have you considered using the Range.AdvancedFilter method
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes....)
"John Smith"
wrote in messagenews:
...
I have a spreadsheet (XL 2007) that contains about 16,000 records. I
need the ability to extract records based upon anywhere from 1 to 3
fields and print each record. The fields are selected from a userform
that contains 3 combo boxes – combo1 contains Supervisor’s names,
combo2 contains Employee’s, and combo3 contains a work location. What
is the best way to locate the records based upon the criteria selected
by the User?
Thanks.
James

Thanks Jim, I will look into that option. Is this something that I can
attach to a command button to trigger it?
James- Hide quoted text -

- Show quoted text -

Thanks Jim, but I guess I don't fully understand the use of the
advanced filter functionality. I had originally tried doing this using
auto filter, since it is what I was familiar with:

Private Sub CommandButton1_Click()
Dim copyrange As Range, c As Range, rowcnt As Long
Dim myvar1 As String, myvar2 As String, myvar3 As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False


myvar1 = UserForm1.TeacherCombo.Value
myvar2 = UserForm1.StudentCombo.Value
myvar3 = UserForm1.PeriodCombo.Value
'nef

With Worksheets("sheet3")
Set myrange = .Range("d1", .Cells(.Rows.Count, "d").End(xlUp))
If Not Worksheets("sheet3").AutoFilterMode Then
Worksheets("sheet3").Range("A1").AutoFilter
End If

Selection.AutoFilter Field:=1, Criteria1:=myvar1, _
Operator:=xlAnd, Criteria2:=myvar2, Operator:=xlAnd,
Criteria3:=myvar3
End With
Worksheets("Sheet3").AutoFilterMode = False

Unload Me
End Sub

The problem with this code is that it doesn't work. The data is in
sheet 3 and I want it displayed in sheet4. If I select a value in the
first combo box and leave the other two blank it returns every single
record. How can I apply advanced filter to make this work?
James

Again, it is "Range.AdvancedFilter", a new method in xl2007.
It is listed in Help, but that is not much help.
"Range.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)"

Working code looks like...
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("B4:F16000")
rng.AdvancedFilter xlFilterCopy, Worksheets("Sheet1").Range("B1:F2"), _
Worksheets("Sheet2").Range("B2"), False

The crucial part is setting up the Criteria range that tells Excel what to filter.
First your list must have a filled header row.
The Criteria range must have a first row that is the same as the list header row.
The second row of the Criteria range has the filter items filled in (blank if not filtered)
The code sample above uses a four column /16000 row list and a four column/two row criteria range.

So your Criteria range might consist of...
Supervisor | Employee | ID Num | Location
Smith Jones Portland

The second row of the criteria range would be cleared and data entered when the userform Continue/OK
button is clicked.
'---
Jim Cone




"John Smith" wrote in message
...
On Jan 5, 10:26 am, "Jim Cone" wrote:
You already have a userform, so use the OK/Continue button on it.
When the button is clicked, use the values entered into the combo boxes as the filter criteria and
filter the list.
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware
(Greatest Film List.xls - in the free folder)




"John Smith"
wrote in ...
On Jan 4, 10:00 pm, "Jim Cone" wrote:
Have you considered using the Range.AdvancedFilter method
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)
"John Smith"
wrote in messagenews:
...
I have a spreadsheet (XL 2007) that contains about 16,000 records. I
need the ability to extract records based upon anywhere from 1 to 3
fields and print each record. The fields are selected from a userform
that contains 3 combo boxes – combo1 contains Supervisor’s names,
combo2 contains Employee’s, and combo3 contains a work location. What
is the best way to locate the records based upon the criteria selected
by the User?
Thanks.
James

Thanks Jim, I will look into that option. Is this something that I can
attach to a command button to trigger it?
James- Hide quoted text -

- Show quoted text -

Thanks Jim, but I guess I don't fully understand the use of the
advanced filter functionality. I had originally tried doing this using
auto filter, since it is what I was familiar with:

Private Sub CommandButton1_Click()
Dim copyrange As Range, c As Range, rowcnt As Long
Dim myvar1 As String, myvar2 As String, myvar3 As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False


myvar1 = UserForm1.TeacherCombo.Value
myvar2 = UserForm1.StudentCombo.Value
myvar3 = UserForm1.PeriodCombo.Value
'nef

With Worksheets("sheet3")
Set myrange = .Range("d1", .Cells(.Rows.Count, "d").End(xlUp))
If Not Worksheets("sheet3").AutoFilterMode Then
Worksheets("sheet3").Range("A1").AutoFilter
End If

Selection.AutoFilter Field:=1, Criteria1:=myvar1, _
Operator:=xlAnd, Criteria2:=myvar2, Operator:=xlAnd,
Criteria3:=myvar3
End With
Worksheets("Sheet3").AutoFilterMode = False

Unload Me
End Sub

The problem with this code is that it doesn't work. The data is in
sheet 3 and I want it displayed in sheet4. If I select a value in the
first combo box and leave the other two blank it returns every single
record. How can I apply advanced filter to make this work?
James

hi James,

__Help Excel 2002_______________________________________

AdvancedFilter Method

This method filters or copies data from a list based on a criteria range.
If the initial selection is a single cell, the current zone of this cell is used. Variant data type.

expression.AdvancedFilter (Action, CriteriaRange, CopyToRange, Unique)

expression Required. An expression that returns one of the objects listed in the Applies To list.

Action Argument XlFilterAction mandatory.

XlFilterAction can be one of these constants XlFilterAction.
xlFilterCopy
xlFilterInPlace

CriteriaRange Optional Variant. The criteria range. If you do not specify this argument, there is no criterion.

CopyToRange Optional Variant. The destination range of rows copied if the argument is set xlFilterCopy Action.
Otherwise, this argument is ignored.

Unique Variant optional. Set it to True to filter unique records only, and False to filter all records that meet the criteria.
The default value is False.

example

This example shows how to filter a database (called "Database") based on a criteria range named "Criteria."

Range("Database").AdvancedFilter _
Action:= xlFilterInPlace, _
CriteriaRange:= Range("Criteria")
'_________________________________________________ ________


don't forget to name the ranges of cells, and to use these names



--
isabelle


Le 2012-01-05 14:58, John Smith a écrit :


The problem with this code is that it doesn't work. The data is in
sheet 3 and I want it displayed in sheet4. If I select a value in the
first combo box and leave the other two blank it returns every single
record. How can I apply advanced filter to make this work?
James