On Jan 5, 6:35*pm, "Jim Cone" wrote:
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- Hide quoted text -
- Show quoted text -
Jim,
That worked great - you're a genius. But how do I get it to search
multiple fields? Lets say a supervisor (Jones, Dale E.) has an
emplyee (Thomas, Mark) and he wants to see how many jobs he has
charged to a specific work area (4544). The employee has transferred
to a different department during the year, but it's only the time
charged while he has been working for his current supervisor that the
auditors want to see.