Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables into autofilters
I know I can input an autofilter such as:
AutoFilterMode = False .Range("A1:D1").AutoFilter .Range("A1:D1").AutoFilter Field:=2, Criteria1:="=35", _ Operator:=xlAnd, Criteria2:="<=45" But how do I get the two criteria to be read from variables whose values are input by the user such as a Start_date and an End_date? TIA. Zippy. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables into autofilters
Hi Zippy
input by the user such as a Start_date and an End_date? Tip from this page http://www.rondebruin.nl/copy5.htm Or use two cells on your worksheet with the dates rng.AutoFilter Field:=4, Criteria1:="=" & CLng(Range("B1").Value), _ Operator:=xlAnd, Criteria2:="<=" & CLng(Range("C1").Value) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Zippy" wrote in message ... I know I can input an autofilter such as: AutoFilterMode = False .Range("A1:D1").AutoFilter .Range("A1:D1").AutoFilter Field:=2, Criteria1:="=35", _ Operator:=xlAnd, Criteria2:="<=45" But how do I get the two criteria to be read from variables whose values are input by the user such as a Start_date and an End_date? TIA. Zippy. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables into autofilters
AutoFilterMode = False
.Range("A1:D1").AutoFilter .Range("A1:D1").AutoFilter Field:=2, _ Criteria1:="=" & StartDate, _ Operator:=xlAnd, Criteria2:="<=" & EndDate -- Regards, Tom Ogilvy "Zippy" wrote: I know I can input an autofilter such as: AutoFilterMode = False .Range("A1:D1").AutoFilter .Range("A1:D1").AutoFilter Field:=2, Criteria1:="=35", _ Operator:=xlAnd, Criteria2:="<=45" But how do I get the two criteria to be read from variables whose values are input by the user such as a Start_date and an End_date? TIA. Zippy. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables into autofilters
Use an InputBox to prompt the user for the start and end dates, then
simply concantenate them into the criteria expressions (the returned result from the InputBox is a string), like so (using a UserForm would entail a lot more work, but you could get both values, do checking on them together, etc.): Dim strStartDate As String Dim strEndDate As String strStartDate = InputBox("Enter the start date:", "AutoFilter this List") strEndDate = InputBox("Enter the end date:", "AutoFilter this List") .Range("A1:D1").AutoFilter Field:=2, _ Criteria1:="=" & strStartDate, _ Operator:=xlAnd, _ Criteria2:="<=" & strEndDate You might want to check the values in the above code before calling the AutoFilter method (valid dates, etc.). Also, I don't think you need the following lines in your code, but you can test to be su AutoFilterMode = False .Range("A1:D1").AutoFilter -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables into autofilters
That worked a treat. Thanks guys.
Zippy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFilters | Excel Worksheet Functions | |||
How to set up AutoFilters? | Excel Worksheet Functions | |||
autofilters | Excel Worksheet Functions | |||
VBA and Autofilters | Excel Programming | |||
Userforms and autofilters - Autofilters don't seen to work with userform | Excel Programming |