Extract Records via Userform

[John Smith]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[John Smith]

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" <[email protected]>
wrote in messageI 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

 

[Jim Cone]

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" <[email protected]>
wrote in message

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" <[email protected]>
wrote in (e-mail address removed)...
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

 

[John Smith]

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" <[email protected]>
wrote in message

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

 

[Jim Cone]

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

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" <[email protected]>
wrote in message

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

 

[isabelle]

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

 

[John Smith]

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






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.

 

[John Smith]

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






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 -

Sorry, Jim, I got it to work. Thanks for all of your help.

 

[Jim Cone]

Glad you got it working. Microsoft should get most of the credit here.

The criteria range can have multiple rows and deliver some strange combinations of data.
You just have to be able to understand the rules (that's what it is all about, isn't it). <grin>

Start at the Debra Dalgleish website under the subject of...
"Setting up the Advanced FilterCriteria Range" at http://www.contextures.com/xladvfilter01_2003.html
Also see "Examples of complex criteria" at...
http://office.microsoft.com/en-us/e...ria-HA001218694.aspx?CTT=1&origin=EC001022984

In addition, the Excel (not vba) Database functions use the same type of criteria rules.
These are a much ignored set of functions that can be extremely valuable.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Bingo Card Variety .xls workbook - in the free folder)




"John Smith" <[email protected]>
wrote in message
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.

 

[John Smith]

Glad you got it working.  Microsoft should get most of the credit here.

The criteria range can have multiple rows and deliver some strange combinations of data.
You just have to be able to understand the rules  (that's what it is all about, isn't it). <grin>

Start at the Debra Dalgleish website under the subject of...
"Setting up the Advanced FilterCriteria Range" athttp://www.contextures.com/xladvfilter01_2003.html
Also see "Examples of complex criteria" at...http://office.microsoft.com/en-us/excel-help/examples-of-complex-crit...

In addition, the Excel (not vba) Database functions use the same type of criteria rules.
These are a much ignored set of functions that can be extremely valuable.
'---
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(Bingo Card Variety .xls workbook - in the free folder)

"John Smith" <[email protected]>
wrote in messageJim,
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.

Just out of curiosity, is there a way to build a list for a combo box
by selecting the employees associated their supervisor at the moment
the supervisor is selected? If the User selects Jones, Dale E. in the
supervisor combo box, then when the User moves to the employee combo
box (which triggers the code), only those employees associated with
Jones, Dale E. will be displayed in the employee combo box. I would
think that I could use the Advance Filter functionality to take care
of it.
James

 

[Jim Cone]

One way...
"How to Populate One List Box Based on Another List Box"
http://support.microsoft.com/kb/213748
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)




"John Smith" <[email protected]>
wrote in message
Just out of curiosity, is there a way to build a list for a combo box
by selecting the employees associated their supervisor at the moment
the supervisor is selected? If the User selects Jones, Dale E. in the
supervisor combo box, then when the User moves to the employee combo
box (which triggers the code), only those employees associated with
Jones, Dale E. will be displayed in the employee combo box. I would
think that I could use the Advance Filter functionality to take care
of it.
James

 

[John Smith]

One way...
"How to Populate One List Box Based on Another List Box"http://support.microsoft.com/kb/213748
--
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in:  compares, matches, counts, lists, finds, deletes....)

"John Smith" <[email protected]>
wrote in messageJust out of curiosity, is there a way to build a list for a combo box
by selecting the employees associated their supervisor at the moment
the supervisor is selected? If the User selects  Jones, Dale E. in the
supervisor combo box, then when the User moves to the employee combo
box (which triggers the code), only those employees associated with
Jones, Dale E. will be displayed in the employee combo box. I would
think that I could use the Advance Filter functionality to take care
of it.
James

Thanks Jim, I'll give that a try.