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

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

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" <(E-Mail Removed)> skrev i
meddelelsen news:8778065E-5D1A-418B-96FE-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> skrev i
> meddelelsen news:8778065E-5D1A-418B-96FE-(E-Mail Removed)...
> > 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
>
>

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" <(E-Mail Removed)> skrev i
meddelelsen news:03597270-C65A-4715-81AD-(E-Mail Removed)...
> 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" <(E-Mail Removed)> skrev i
>> meddelelsen news:8778065E-5D1A-418B-96FE-(E-Mail Removed)...
>> > 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
>>
>>

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" <(E-Mail Removed)> wrote in
message news:8778065E-5D1A-418B-96FE-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> skrev i
> meddelelsen news:03597270-C65A-4715-81AD-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> skrev i
> >> meddelelsen news:8778065E-5D1A-418B-96FE-(E-Mail Removed)...
> >> > 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
> >>
> >>
>
>

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" <(E-Mail Removed)> skrev i
> > meddelelsen news:03597270-C65A-4715-81AD-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> skrev i
> > >> meddelelsen news:8778065E-5D1A-418B-96FE-(E-Mail Removed)...
> > >> > 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
> > >>
> > >>
> >
> >

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" <(E-Mail Removed)> wrote in
> message news:8778065E-5D1A-418B-96FE-(E-Mail Removed)...
> > 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
>
>

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" <(E-Mail Removed)> skrev i
> > meddelelsen news:03597270-C65A-4715-81AD-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> skrev i
> > >> meddelelsen news:8778065E-5D1A-418B-96FE-(E-Mail Removed)...
> > >> > 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
> > >>
> > >>
> >
> >