Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mulitple Filter Selection
Hi,
I need to strenghten my code to allow the users to select 2 areas (or columns) in which the filter would operate. I was able to do one, but can't find a way to do more than one. ' Dim Message, Title, MyValue Dim ExactDate As Date Dim ExactCriterion As String Message = "Please Enter the Period, (00)" Title = "Title" MyValue = InputBox(Message, Title) ExactCriterion = Format(ExactDate, "mm/dd/yy") Range("Headers").AutoFilter Range("Headers").AutoFilter Field:=6, Criteria1:=MyValue Please help. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mulitple Filter Selection
A sheet can only have one filter applied. If you want multiple areas, use multiple sheets, or
combine the ranges and use multiple criteria (on different columns) HTH, Bernie MS Excel MVP "MrRJ" wrote in message ... Hi, I need to strenghten my code to allow the users to select 2 areas (or columns) in which the filter would operate. I was able to do one, but can't find a way to do more than one. ' Dim Message, Title, MyValue Dim ExactDate As Date Dim ExactCriterion As String Message = "Please Enter the Period, (00)" Title = "Title" MyValue = InputBox(Message, Title) ExactCriterion = Format(ExactDate, "mm/dd/yy") Range("Headers").AutoFilter Range("Headers").AutoFilter Field:=6, Criteria1:=MyValue Please help. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mulitple Filter Selection
Bernie,
If I understood you correctly, there is no way that I can ask the user to provide filter criteria? I can build a macro for variable selected filters, see below. Selection.AutoFilter Field:=6, Criteria1:="13" Selection.AutoFilter Field:=7, Criteria1:="08" Selection.AutoFilter Field:=9, Criteria1:="Direct" Selection.AutoFilter Field:=11, Criteria1:="PBGCAN" Selection.AutoFilter Field:=20, Criteria1:="<NA", Operator:=xlAnd With this concept, can I have the user be prompt to type in a value for 2 different columns? Any ideas? "Bernie Deitrick" wrote: A sheet can only have one filter applied. If you want multiple areas, use multiple sheets, or combine the ranges and use multiple criteria (on different columns) HTH, Bernie MS Excel MVP "MrRJ" wrote in message ... Hi, I need to strenghten my code to allow the users to select 2 areas (or columns) in which the filter would operate. I was able to do one, but can't find a way to do more than one. ' Dim Message, Title, MyValue Dim ExactDate As Date Dim ExactCriterion As String Message = "Please Enter the Period, (00)" Title = "Title" MyValue = InputBox(Message, Title) ExactCriterion = Format(ExactDate, "mm/dd/yy") Range("Headers").AutoFilter Range("Headers").AutoFilter Field:=6, Criteria1:=MyValue Please help. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mulitple Filter Selection
No. You can ask easily:
Selection.AutoFilter Field:=6, Criteria1:=CStr(InputBox("Criteria for Field 1")) Selection.AutoFilter Field:=7, Criteria1:=CStr(InputBox("Criteria for Field 2")) etc... HTH, Bernie MS Excel MVP "MrRJ" wrote in message ... Bernie, If I understood you correctly, there is no way that I can ask the user to provide filter criteria? I can build a macro for variable selected filters, see below. Selection.AutoFilter Field:=6, Criteria1:="13" Selection.AutoFilter Field:=7, Criteria1:="08" Selection.AutoFilter Field:=9, Criteria1:="Direct" Selection.AutoFilter Field:=11, Criteria1:="PBGCAN" Selection.AutoFilter Field:=20, Criteria1:="<NA", Operator:=xlAnd With this concept, can I have the user be prompt to type in a value for 2 different columns? Any ideas? "Bernie Deitrick" wrote: A sheet can only have one filter applied. If you want multiple areas, use multiple sheets, or combine the ranges and use multiple criteria (on different columns) HTH, Bernie MS Excel MVP "MrRJ" wrote in message ... Hi, I need to strenghten my code to allow the users to select 2 areas (or columns) in which the filter would operate. I was able to do one, but can't find a way to do more than one. ' Dim Message, Title, MyValue Dim ExactDate As Date Dim ExactCriterion As String Message = "Please Enter the Period, (00)" Title = "Title" MyValue = InputBox(Message, Title) ExactCriterion = Format(ExactDate, "mm/dd/yy") Range("Headers").AutoFilter Range("Headers").AutoFilter Field:=6, Criteria1:=MyValue Please help. Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mulitple Filter Selection
Bernie,
Through some trial and error, I managed to pull it off. Here is the code I used. Dim Message, Title, MyValuePeriod Dim ExactDate As Date Message = "Please Enter the Period, (00)" Title = "Title" MyValuePeriod = InputBox(Message, Title) Range("Headers").AutoFilter Range("Headers").AutoFilter Field:=6, Criteria1:=MyValuePeriod Message = "Please Enter the Year Reported, (00)" Title = "Title" MyValueYear = InputBox(Message, Title) Range("Headers").AutoFilter Field:=7, Criteria1:=MyValueYear End Sub Thoughts? "MrRJ" wrote: Bernie, If I understood you correctly, there is no way that I can ask the user to provide filter criteria? I can build a macro for variable selected filters, see below. Selection.AutoFilter Field:=6, Criteria1:="13" Selection.AutoFilter Field:=7, Criteria1:="08" Selection.AutoFilter Field:=9, Criteria1:="Direct" Selection.AutoFilter Field:=11, Criteria1:="PBGCAN" Selection.AutoFilter Field:=20, Criteria1:="<NA", Operator:=xlAnd With this concept, can I have the user be prompt to type in a value for 2 different columns? Any ideas? "Bernie Deitrick" wrote: A sheet can only have one filter applied. If you want multiple areas, use multiple sheets, or combine the ranges and use multiple criteria (on different columns) HTH, Bernie MS Excel MVP "MrRJ" wrote in message ... Hi, I need to strenghten my code to allow the users to select 2 areas (or columns) in which the filter would operate. I was able to do one, but can't find a way to do more than one. ' Dim Message, Title, MyValue Dim ExactDate As Date Dim ExactCriterion As String Message = "Please Enter the Period, (00)" Title = "Title" MyValue = InputBox(Message, Title) ExactCriterion = Format(ExactDate, "mm/dd/yy") Range("Headers").AutoFilter Range("Headers").AutoFilter Field:=6, Criteria1:=MyValue Please help. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Default Filter Selection to None | Excel Discussion (Misc queries) | |||
How do I filter or search mulitple for text strings? | Excel Discussion (Misc queries) | |||
Database - mulitple selection | Excel Worksheet Functions | |||
Copy selected mulitple worksheets to mulitple new workbooks | Excel Programming | |||
Copy selected mulitple worksheets to mulitple new workbooks | Excel Programming |