Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
Hi There,
Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
Try the below
Sub Macro() Dim dtStart As Date Dim dtEnd As Date dtStart = Workbooks("Other File.xls").Sheets("Sheet1").Range("A3") dtEnd = Workbooks("Other File.xls").Sheets("Sheet1").Range("A9") Selection.AutoFilter Field:=4, Criteria1:= _ "" & dtStart, Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<=" & dtEnd, Operator:=xlAnd End Sub If this post helps click Yes --------------- Jacob Skaria "Hilvert Scheper" wrote: Hi There, Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
Hi
You are trying to filter for the text "'[Other File...." , not for the value in A3. Look at this: Set wb = Workbooks("Other File.xls") Set sh = wb.Worksheets("Sheet1") Crit1 = sh.Range("A3").Value Crit2 = sh.Range("A9").Value Selection.AutoFilter Field:=4, Criteria1:= _ "" & Crit1, _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<=" & Crit2, _ Operator:=xlAnd Hopes this helps. --- Per "Hilvert Scheper" skrev i meddelelsen ... Hi There, Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
Dear Jacob and Per,
Thank You so much for Your ideas BUT... The Filter still does not show anything; "0 Records of 1416 found", where it should show 225 records... Sorry!! Any ideas Please? Rgds, Hilvert "Per Jessen" wrote: Hi You are trying to filter for the text "'[Other File...." , not for the value in A3. Look at this: Set wb = Workbooks("Other File.xls") Set sh = wb.Worksheets("Sheet1") Crit1 = sh.Range("A3").Value Crit2 = sh.Range("A9").Value Selection.AutoFilter Field:=4, Criteria1:= _ "" & Crit1, _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<=" & Crit2, _ Operator:=xlAnd Hopes this helps. --- Per "Hilvert Scheper" skrev i meddelelsen ... Hi There, Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
Thanks for your reply,
Looking a bit closer to your filter statements, as you are trying to set up two conditions for one column, it shall be done in one statement: Selection.AutoFilter Field:=4, _ Criteria1:= "" & Crit1, _ Operator:=xlAnd, _ Criteria2:= "<=" & Crit2 Hopes this helps. --- Per "Hilvert Scheper" skrev i meddelelsen ... Dear Jacob and Per, Thank You so much for Your ideas BUT... The Filter still does not show anything; "0 Records of 1416 found", where it should show 225 records... Sorry!! Any ideas Please? Rgds, Hilvert "Per Jessen" wrote: Hi You are trying to filter for the text "'[Other File...." , not for the value in A3. Look at this: Set wb = Workbooks("Other File.xls") Set sh = wb.Worksheets("Sheet1") Crit1 = sh.Range("A3").Value Crit2 = sh.Range("A9").Value Selection.AutoFilter Field:=4, Criteria1:= _ "" & Crit1, _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<=" & Crit2, _ Operator:=xlAnd Hopes this helps. --- Per "Hilvert Scheper" skrev i meddelelsen ... Hi There, Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
when using autofilter from the sheet data, you'll see dates in the dropdown
for values. In code, when you select a date, you'll see the excel internal number. like 40008 for today so in code you need to convert, eg Option Explicit Sub setFilter() Dim sFilter As String sFilter = "=" & Format$(Workbooks("Book2").Worksheets(1).Range("C3 "), "dd/mm/yyyy") Selection.AutoFilter Selection.AutoFilter Field:=6, Criteria1:=sFilter, Operator:=xlAnd End Sub NOTICE I used "=" since there's no operator equivalent. "Hilvert Scheper" wrote in message ... Hi There, Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
Dear Per and Jacob,
Many Thanks again to You Both for Your help, Whatever I try, the filter Won't show the 225 entries that I need, "0 records found". Very Frustrating, I think I'll just give up for now, looks like I'm not getting anywhere with this. Question; Why do You put "" in the first Criteria (without the "="?), and "<=" in the second, is there any logic to this? Just curious that's all. Kind Regards, Hilvert "Per Jessen" wrote: Thanks for your reply, Looking a bit closer to your filter statements, as you are trying to set up two conditions for one column, it shall be done in one statement: Selection.AutoFilter Field:=4, _ Criteria1:= "" & Crit1, _ Operator:=xlAnd, _ Criteria2:= "<=" & Crit2 Hopes this helps. --- Per "Hilvert Scheper" skrev i meddelelsen ... Dear Jacob and Per, Thank You so much for Your ideas BUT... The Filter still does not show anything; "0 Records of 1416 found", where it should show 225 records... Sorry!! Any ideas Please? Rgds, Hilvert "Per Jessen" wrote: Hi You are trying to filter for the text "'[Other File...." , not for the value in A3. Look at this: Set wb = Workbooks("Other File.xls") Set sh = wb.Worksheets("Sheet1") Crit1 = sh.Range("A3").Value Crit2 = sh.Range("A9").Value Selection.AutoFilter Field:=4, Criteria1:= _ "" & Crit1, _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<=" & Crit2, _ Operator:=xlAnd Hopes this helps. --- Per "Hilvert Scheper" skrev i meddelelsen ... Hi There, Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
Ok. Let us try this..
1. In a new workbook enter dummy data in ColA and B as below. Make sure the dates are in excel date format. Shortcut to assign todays date (Ctrl+;). In cell C2 and D2 you have start and end dates.. ColA ColB ColC ColD Date Day StartDate EndDate 7/1/2009 1 7/2/2009 7/7/2009 7/2/2009 2 7/3/2009 3 7/4/2009 4 7/5/2009 5 7/6/2009 6 7/7/2009 7 7/8/2009 8 7/9/2009 9 7/10/2009 10 2. Launch VBE using Alt+F11. Insert a module and paste the below code.. Sub Macro() Dim dtStart As Date, dtEnd As Date dtStart = Range("D1") dtEnd = Range("E1") Selection.AutoFilter Field:=1, _ Criteria1:="" & dtStart, Operator:=xlAnd, _ Criteria2:="<=" & dtEnd End Sub 3. Select columns A and B and run the macro to see what happens....It should filter column 1 to display dates between start date and end date. PS: "" and "<" signs are there in the code which you pasted which denot greater than and less than,.. If this post helps click Yes --------------- Jacob Skaria "Hilvert Scheper" wrote: Dear Per and Jacob, Many Thanks again to You Both for Your help, Whatever I try, the filter Won't show the 225 entries that I need, "0 records found". Very Frustrating, I think I'll just give up for now, looks like I'm not getting anywhere with this. Question; Why do You put "" in the first Criteria (without the "="?), and "<=" in the second, is there any logic to this? Just curious that's all. Kind Regards, Hilvert "Per Jessen" wrote: Thanks for your reply, Looking a bit closer to your filter statements, as you are trying to set up two conditions for one column, it shall be done in one statement: Selection.AutoFilter Field:=4, _ Criteria1:= "" & Crit1, _ Operator:=xlAnd, _ Criteria2:= "<=" & Crit2 Hopes this helps. --- Per "Hilvert Scheper" skrev i meddelelsen ... Dear Jacob and Per, Thank You so much for Your ideas BUT... The Filter still does not show anything; "0 Records of 1416 found", where it should show 225 records... Sorry!! Any ideas Please? Rgds, Hilvert "Per Jessen" wrote: Hi You are trying to filter for the text "'[Other File...." , not for the value in A3. Look at this: Set wb = Workbooks("Other File.xls") Set sh = wb.Worksheets("Sheet1") Crit1 = sh.Range("A3").Value Crit2 = sh.Range("A9").Value Selection.AutoFilter Field:=4, Criteria1:= _ "" & Crit1, _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<=" & Crit2, _ Operator:=xlAnd Hopes this helps. --- Per "Hilvert Scheper" skrev i meddelelsen ... Hi There, Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
Dear Patrick,
Also to You a Huge Thanks for Your interference, I have tried Your suggestion However Whetever I try, Nothing seems to work. I just give up for now, can't afford to spend more time on this than I already have, pretty close to a full day and not getting anywhere. Many Thanks, and SORRY! Hilvert "Patrick Molloy" wrote: when using autofilter from the sheet data, you'll see dates in the dropdown for values. In code, when you select a date, you'll see the excel internal number. like 40008 for today so in code you need to convert, eg Option Explicit Sub setFilter() Dim sFilter As String sFilter = "=" & Format$(Workbooks("Book2").Worksheets(1).Range("C3 "), "dd/mm/yyyy") Selection.AutoFilter Selection.AutoFilter Field:=6, Criteria1:=sFilter, Operator:=xlAnd End Sub NOTICE I used "=" since there's no operator equivalent. "Hilvert Scheper" wrote in message ... Hi There, Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
Hilvert; I dont think you have tried the code which I posted initially where
the variables are declared as *** Date **** Please note the correction in references .... Sub Macro() Dim dtStart As Date, dtEnd As Date dtStart = Range("C2") dtEnd = Range("D2") Selection.AutoFilter Field:=1, _ Criteria1:="" & dtStart, Operator:=xlAnd, _ Criteria2:="<=" & dtEnd End Sub If this post helps click Yes --------------- Jacob Skaria "Hilvert Scheper" wrote: Dear Per and Jacob, Many Thanks again to You Both for Your help, Whatever I try, the filter Won't show the 225 entries that I need, "0 records found". Very Frustrating, I think I'll just give up for now, looks like I'm not getting anywhere with this. Question; Why do You put "" in the first Criteria (without the "="?), and "<=" in the second, is there any logic to this? Just curious that's all. Kind Regards, Hilvert "Per Jessen" wrote: Thanks for your reply, Looking a bit closer to your filter statements, as you are trying to set up two conditions for one column, it shall be done in one statement: Selection.AutoFilter Field:=4, _ Criteria1:= "" & Crit1, _ Operator:=xlAnd, _ Criteria2:= "<=" & Crit2 Hopes this helps. --- Per "Hilvert Scheper" skrev i meddelelsen ... Dear Jacob and Per, Thank You so much for Your ideas BUT... The Filter still does not show anything; "0 Records of 1416 found", where it should show 225 records... Sorry!! Any ideas Please? Rgds, Hilvert "Per Jessen" wrote: Hi You are trying to filter for the text "'[Other File...." , not for the value in A3. Look at this: Set wb = Workbooks("Other File.xls") Set sh = wb.Worksheets("Sheet1") Crit1 = sh.Range("A3").Value Crit2 = sh.Range("A9").Value Selection.AutoFilter Field:=4, Criteria1:= _ "" & Crit1, _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<=" & Crit2, _ Operator:=xlAnd Hopes this helps. --- Per "Hilvert Scheper" skrev i meddelelsen ... Hi There, Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
if you'd like to send me two excel workbooks , i'll see what i can do
"Hilvert Scheper" wrote in message ... Dear Patrick, Also to You a Huge Thanks for Your interference, I have tried Your suggestion However Whetever I try, Nothing seems to work. I just give up for now, can't afford to spend more time on this than I already have, pretty close to a full day and not getting anywhere. Many Thanks, and SORRY! Hilvert "Patrick Molloy" wrote: when using autofilter from the sheet data, you'll see dates in the dropdown for values. In code, when you select a date, you'll see the excel internal number. like 40008 for today so in code you need to convert, eg Option Explicit Sub setFilter() Dim sFilter As String sFilter = "=" & Format$(Workbooks("Book2").Worksheets(1).Range("C3 "), "dd/mm/yyyy") Selection.AutoFilter Selection.AutoFilter Field:=6, Criteria1:=sFilter, Operator:=xlAnd End Sub NOTICE I used "=" since there's no operator equivalent. "Hilvert Scheper" wrote in message ... Hi There, Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
Saved from a previous post:
This is from "Excel 2002 VBA Programmer's Reference" Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg http://www.oaltd.co.uk:80/ExcelProgR...rogRefCh22.htm Search for "Range.AutoFilter" and you'll see this note: Range.AutoFilter The AutoFilter method of a Range object is a very curious beast. We are forced to pass it strings for its filter criteria and hence must be aware of its string handling behaviour. The criteria string consists of an operator (=, , <, = etc.) followed by a value. If no operator is specified, the "=" operator is assumed. The key issue is that when using the "=" operator, AutoFilter performs a textual match, while using any other operator results in a match by value. This gives us problems when trying to locate exact matches for dates and numbers. If we use "=", Excel matches on the text that is displayed in the cell, i.e. the formatted number. As the text displayed in a cell will change with different regional settings and Windows language version, it is impossible for us to create a criteria string that will locate an exact match in all locales. There is a workaround for this problem. When using any of the other filter criteria, Excel plays by the rules and interprets the criteria string according to US formats. Hence, a search criterion of "=02/01/2001" will find all dates on or after 1st Feb, 2001, in all locales. We can use this to match an exact date by using two AutoFilter criteria. The following code will give an exact match on 1st Feb, 2001 and will work in any locale: Range("A1:D200").AutoFilter 2, "=02/01/2001", xlAnd, "<=02/01/2001" ========== So you may want something like: Selection.AutoFilter Field:=4, _ Criteria1:="=" & format(startFilter, "mm/dd/yyyy") Operator:=xlAnd, _ Criteria2:="<" & format(endFilter, "mm/dd/yyyy") Hilvert Scheper wrote: Hi There, Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
Important: Use always the US mm/dd/yyyy format if you filter Dates.
Note: You only have the use the mm/dd/yyyy format in the code, no problem if the format in the worksheet is different. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Patrick Molloy" wrote in message ... if you'd like to send me two excel workbooks , i'll see what i can do "Hilvert Scheper" wrote in message ... Dear Patrick, Also to You a Huge Thanks for Your interference, I have tried Your suggestion However Whetever I try, Nothing seems to work. I just give up for now, can't afford to spend more time on this than I already have, pretty close to a full day and not getting anywhere. Many Thanks, and SORRY! Hilvert "Patrick Molloy" wrote: when using autofilter from the sheet data, you'll see dates in the dropdown for values. In code, when you select a date, you'll see the excel internal number. like 40008 for today so in code you need to convert, eg Option Explicit Sub setFilter() Dim sFilter As String sFilter = "=" & Format$(Workbooks("Book2").Worksheets(1).Range("C3 "), "dd/mm/yyyy") Selection.AutoFilter Selection.AutoFilter Field:=6, Criteria1:=sFilter, Operator:=xlAnd End Sub NOTICE I used "=" since there's no operator equivalent. "Hilvert Scheper" wrote in message ... Hi There, Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Criteria in VBA
hmm .... UK date format worked fine for me.
"Ron de Bruin" wrote in message ... Important: Use always the US mm/dd/yyyy format if you filter Dates. Note: You only have the use the mm/dd/yyyy format in the code, no problem if the format in the worksheet is different. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Patrick Molloy" wrote in message ... if you'd like to send me two excel workbooks , i'll see what i can do "Hilvert Scheper" wrote in message ... Dear Patrick, Also to You a Huge Thanks for Your interference, I have tried Your suggestion However Whetever I try, Nothing seems to work. I just give up for now, can't afford to spend more time on this than I already have, pretty close to a full day and not getting anywhere. Many Thanks, and SORRY! Hilvert "Patrick Molloy" wrote: when using autofilter from the sheet data, you'll see dates in the dropdown for values. In code, when you select a date, you'll see the excel internal number. like 40008 for today so in code you need to convert, eg Option Explicit Sub setFilter() Dim sFilter As String sFilter = "=" & Format$(Workbooks("Book2").Worksheets(1).Range("C3 "), "dd/mm/yyyy") Selection.AutoFilter Selection.AutoFilter Field:=6, Criteria1:=sFilter, Operator:=xlAnd End Sub NOTICE I used "=" since there's no operator equivalent. "Hilvert Scheper" wrote in message ... Hi There, Can Anyone help me out here Please, I want to Filter a spreadsheet by Two criteria that refer to values in another workbook: Greater than the Date in Cell A3 and Less than the Date in Cell A9. How on earth can I do this? I tried: Selection.AutoFilter Field:=4, Criteria1:= _ "'[Other File.xls]Sheet1'!$A$3", _ Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:= _ "<='[Other File.xls]Sheet1'!$A$9", _ Operator:=xlAnd and this doesn't work. The Filter is on but isn't showing Any entries... I Really appreciate Your help!! Rgds, Hilvert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter Criteria | Excel Programming | |||
AutoFilter Criteria in VBA | Excel Programming | |||
autofilter criteria | Excel Programming | |||
Selection.AutoFilter Field / Criteria = criteria sometimes non-existing on worksheet | Excel Programming | |||
Autofilter criteria not yet known | Excel Programming |