Auto filter Query??

Hi hope this is possible.

I would like to run a macro that uses a custom filter to do the
following


a Date is enetered into j18 (start date) and a date entered into k18
(end date)

is it possible to use auto filter to run a custom macro where as the
criterias are

>= j18 and < k18


How would i accomplish this,

any help with some code would be useful i tried the following without
much success

Sub daterange()

Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
Operator:= _
xlAnd, Criteria2:="<Cell(j18)"
End Sub




HYCH

Steve

K1KKKA
Sat Mar 03 09:56:48 CST 2007

On Mar 3, 3:51 pm, "K1KKKA" <instruct...@hotmail.com> wrote:
> Hi hope this is possible.
>
> I would like to run a macro that uses a custom filter to do the
> following
>
> a Date is enetered into j18 (start date) and a date entered into k18
> (end date)
>
> is it possible to use auto filter to run a custom macro where as the
> criterias are
>
> >= j18 and < k18
>
> How would i accomplish this,
>
> any help with some code would be useful i tried the following without
> much success
>
> Sub daterange()
>
> Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
> Operator:= _
> xlAnd, Criteria2:="<Cell(j18)"
> End Sub
>
> HYCH
>
> Steve

Only just considered that maybe a worksheet change event might be more
beneficial??

Earl
Sat Mar 03 10:13:31 CST 2007

Steve,

First of all, if the J18 criteria is both >= AND <, you would get all the records.

Try this form:

Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("j18")

It's a good idea to name cells like J18, so if they get moved, the macro won't still be
looking for the old cell. If J18 is named CR1, then it would look like:

Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("CR1")

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"K1KKKA" <instructorf@hotmail.com> wrote in message
news:1172937408.385996.256470@z35g2000cwz.googlegroups.com...
> On Mar 3, 3:51 pm, "K1KKKA" <instruct...@hotmail.com> wrote:
>> Hi hope this is possible.
>>
>> I would like to run a macro that uses a custom filter to do the
>> following
>>
>> a Date is enetered into j18 (start date) and a date entered into k18
>> (end date)
>>
>> is it possible to use auto filter to run a custom macro where as the
>> criterias are
>>
>> >= j18 and < k18
>>
>> How would i accomplish this,
>>
>> any help with some code would be useful i tried the following without
>> much success
>>
>> Sub daterange()
>>
>> Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
>> Operator:= _
>> xlAnd, Criteria2:="<Cell(j18)"
>> End Sub
>>
>> HYCH
>>
>> Steve
>
> Only just considered that maybe a worksheet change event might be more
> beneficial??
>

K1KKKA
Sat Mar 03 10:31:13 CST 2007

On Mar 3, 4:13 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> Steve,
>
> First of all, if the J18 criteria is both >= AND <, you would get all the records.
>
> Try this form:
>
> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("j18")
>
> It's a good idea to name cells like J18, so if they get moved, the macro won't still be
> looking for the old cell. If J18 is named CR1, then it would look like:
>
> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("CR1")
>
> --
> Earl Kiosterudwww.smokeylake.com
> -----------------------------------------------------------------------"K1KKKA" <instruct...@hotmail.com> wrote in message
>
> news:1172937408.385996.256470@z35g2000cwz.googlegroups.com...
>
>
>
> > On Mar 3, 3:51 pm, "K1KKKA" <instruct...@hotmail.com> wrote:
> >> Hi hope this is possible.
>
> >> I would like to run a macro that uses a custom filter to do the
> >> following
>
> >> a Date is enetered into j18 (start date) and a date entered into k18
> >> (end date)
>
> >> is it possible to use auto filter to run a custom macro where as the
> >> criterias are
>
> >> >= j18 and < k18
>
> >> How would i accomplish this,
>
> >> any help with some code would be useful i tried the following without
> >> much success
>
> >> Sub daterange()
>
> >> Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
> >> Operator:= _
> >> xlAnd, Criteria2:="<Cell(j18)"
> >> End Sub
>
> >> HYCH
>
> >> Steve
>
> > Only just considered that maybe a worksheet change event might be more
> > beneficial??- Hide quoted text -
>
> - Show quoted text -

Thanks Earl,

Have renamed the relevant cells as Date1 and Date2

Am trying to get the range to recognise the following,
>= Date1
< Date2


Have tried the code below using your suggestion, but to no avail

Could you have a look at and advise please

Sub Daterange1()
Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("Date1"),
Operator _
:=xlAnd, Criteria2:="<" & Range("Date2")
End Sub

Thanks Steve

Dave
Sat Mar 03 11:27:27 CST 2007

Sometimes converting to longs helps:

..., Criteria1:=">=" & clng(Range("Date1").value), ...

or even using the same numberformat as you see on the worksheet:

..., Criteria1:=">=" & format(Range("Date1").value, "mm/dd/yyyy"), ...

(adjust that format to match)

K1KKKA wrote:
>
> On Mar 3, 4:13 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> > Steve,
> >
> > First of all, if the J18 criteria is both >= AND <, you would get all the records.
> >
> > Try this form:
> >
> > Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("j18")
> >
> > It's a good idea to name cells like J18, so if they get moved, the macro won't still be
> > looking for the old cell. If J18 is named CR1, then it would look like:
> >
> > Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("CR1")
> >
> > --
> > Earl Kiosterudwww.smokeylake.com
> > -----------------------------------------------------------------------"K1KKKA" <instruct...@hotmail.com> wrote in message
> >
> > news:1172937408.385996.256470@z35g2000cwz.googlegroups.com...
> >
> >
> >
> > > On Mar 3, 3:51 pm, "K1KKKA" <instruct...@hotmail.com> wrote:
> > >> Hi hope this is possible.
> >
> > >> I would like to run a macro that uses a custom filter to do the
> > >> following
> >
> > >> a Date is enetered into j18 (start date) and a date entered into k18
> > >> (end date)
> >
> > >> is it possible to use auto filter to run a custom macro where as the
> > >> criterias are
> >
> > >> >= j18 and < k18
> >
> > >> How would i accomplish this,
> >
> > >> any help with some code would be useful i tried the following without
> > >> much success
> >
> > >> Sub daterange()
> >
> > >> Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
> > >> Operator:= _
> > >> xlAnd, Criteria2:="<Cell(j18)"
> > >> End Sub
> >
> > >> HYCH
> >
> > >> Steve
> >
> > > Only just considered that maybe a worksheet change event might be more
> > > beneficial??- Hide quoted text -
> >
> > - Show quoted text -
>
> Thanks Earl,
>
> Have renamed the relevant cells as Date1 and Date2
>
> Am trying to get the range to recognise the following,
> >= Date1
> < Date2
>
> Have tried the code below using your suggestion, but to no avail
>
> Could you have a look at and advise please
>
> Sub Daterange1()
> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("Date1"),
> Operator _
> :=xlAnd, Criteria2:="<" & Range("Date2")
> End Sub
>
> Thanks Steve

--

Dave Peterson

Don
Sat Mar 03 11:42:25 CST 2007

try this idea
With Range("A6:H6")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=">=" _
& Range("n1") & "", Operator:=xlAnd _
, Criteria2:="<=" & Range("n2")
End With

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"K1KKKA" <instructorf@hotmail.com> wrote in message
news:1172937106.631802.197490@64g2000cwx.googlegroups.com...
> Hi hope this is possible.
>
> I would like to run a macro that uses a custom filter to do the
> following
>
>
> a Date is enetered into j18 (start date) and a date entered into k18
> (end date)
>
> is it possible to use auto filter to run a custom macro where as the
> criterias are
>
>>= j18 and < k18
>
>
> How would i accomplish this,
>
> any help with some code would be useful i tried the following without
> much success
>
> Sub daterange()
>
> Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
> Operator:= _
> xlAnd, Criteria2:="<Cell(j18)"
> End Sub
>
>
>
>
> HYCH
>
> Steve
>

Earl
Sat Mar 03 14:30:03 CST 2007


-----------------------------------------------------------------------
"K1KKKA" <instructorf@hotmail.com> wrote in message
news:1172939473.565833.198920@n33g2000cwc.googlegroups.com...
> On Mar 3, 4:13 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
>> Steve,
>>
>> First of all, if the J18 criteria is both >= AND <, you would get all the records.
>>
>> Try this form:
>>
>> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("j18")
>>
>> It's a good idea to name cells like J18, so if they get moved, the macro won't still be
>> looking for the old cell. If J18 is named CR1, then it would look like:
>>
>> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("CR1")
>>
>> --
>> Earl Kiosterudwww.smokeylake.com
>> -----------------------------------------------------------------------"K1KKKA"
>> <instruct...@hotmail.com> wrote in message
>>
>> news:1172937408.385996.256470@z35g2000cwz.googlegroups.com...
>>
>>
>>
>> > On Mar 3, 3:51 pm, "K1KKKA" <instruct...@hotmail.com> wrote:
>> >> Hi hope this is possible.
>>
>> >> I would like to run a macro that uses a custom filter to do the
>> >> following
>>
>> >> a Date is enetered into j18 (start date) and a date entered into k18
>> >> (end date)
>>
>> >> is it possible to use auto filter to run a custom macro where as the
>> >> criterias are
>>
>> >> >= j18 and < k18
>>
>> >> How would i accomplish this,
>>
>> >> any help with some code would be useful i tried the following without
>> >> much success
>>
>> >> Sub daterange()
>>
>> >> Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
>> >> Operator:= _
>> >> xlAnd, Criteria2:="<Cell(j18)"
>> >> End Sub
>>
>> >> HYCH
>>
>> >> Steve
>>
>> > Only just considered that maybe a worksheet change event might be more
>> > beneficial??- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks Earl,
>
> Have renamed the relevant cells as Date1 and Date2
>
> Am trying to get the range to recognise the following,
>>= Date1
> < Date2
>
>
> Have tried the code below using your suggestion, but to no avail
>
> Could you have a look at and advise please
>
> Sub Daterange1()
> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("Date1"),
> Operator _
> :=xlAnd, Criteria2:="<" & Range("Date2")
> End Sub
>
> Thanks Steve


Steve,

This code works for me. I get the records between the dates specified in J18 and J19. It
doesn't matter how the dates are formatted either in the table or in the criteria cells.

Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("j18"), Operator:=xlAnd,
Criteria2:="<" & Range("J19")

Be sure that the stuff in the table and in your criteria cells are truly dates. If you
remove all formatting (Edit - Clear - Formats), you should see numbers. Feb 1, 2007 is
39114. Etc.

Say what does or doesn't happen when you run your code. Error messages? We're a bit in the
dark.

--
Earl Kiosterud
www.smokeylake.com

K1KKKA
Sat Mar 03 17:54:25 CST 2007

On Mar 3, 8:30 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> -----------------------------------------------------------------------"K=
1KKKA" <instruct...@hotmail.com> wrote in message
>
> news:1172939473.565833.198920@n33g2000cwc.googlegroups.com...
>
>
>
>
>
> > On Mar 3, 4:13 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> >> Steve,
>
> >> First of all, if the J18 criteria is both >=3D AND <, you would get al=
l the records.
>
> >> Try this form:
>
> >> Selection.AutoFilter Field:=3D2, Criteria1:=3D">=3D" & Range("j18")
>
> >> It's a good idea to name cells like J18, so if they get moved, the mac=
ro won't still be
> >> looking for the old cell. If J18 is named CR1, then it would look lik=
e:
>
> >> Selection.AutoFilter Field:=3D2, Criteria1:=3D">=3D" & Range("CR1=
")
>
> >> --
> >> Earl Kiosterudwww.smokeylake.com
> >> ----------------------------------------------------------------------=
-"K1K=ADKKA"
> >> <instruct...@hotmail.com> wrote in message
>
> >>news:1172937408.385996.256470@z35g2000cwz.googlegroups.com...
>
> >> > On Mar 3, 3:51 pm, "K1KKKA" <instruct...@hotmail.com> wrote:
> >> >> Hi hope this is possible.
>
> >> >> I would like to run a macro that uses a custom filter to do the
> >> >> following
>
> >> >> a Date is enetered into j18 (start date) and a date entered into k18
> >> >> (end date)
>
> >> >> is it possible to use auto filter to run a custom macro where as the
> >> >> criterias are
>
> >> >> >=3D j18 and < k18
>
> >> >> How would i accomplish this,
>
> >> >> any help with some code would be useful i tried the following witho=
ut
> >> >> much success
>
> >> >> Sub daterange()
>
> >> >> Selection.AutoFilter Field:=3D2, Criteria1:=3D">=3DCell(j18)",
> >> >> Operator:=3D _
> >> >> xlAnd, Criteria2:=3D"<Cell(j18)"
> >> >> End Sub
>
> >> >> HYCH
>
> >> >> Steve
>
> >> > Only just considered that maybe a worksheet change event might be mo=
re
> >> > beneficial??- Hide quoted text -
>
> >> - Show quoted text -
>
> > Thanks Earl,
>
> > Have renamed the relevant cells as Date1 and Date2
>
> > Am trying to get the range to recognise the following,
> >>=3D Date1
> > < Date2
>
> > Have tried the code below using your suggestion, but to no avail
>
> > Could you have a look at and advise please
>
> > Sub Daterange1()
> > Selection.AutoFilter Field:=3D2, Criteria1:=3D">=3D" & Range("Date1"=
),
> > Operator _
> > :=3DxlAnd, Criteria2:=3D"<" & Range("Date2")
> > End Sub
>
> > Thanks Steve
>
> Steve,
>
> This code works for me. I get the records between the dates specified in=
J18 and J19. It
> doesn't matter how the dates are formatted either in the table or in the =
criteria cells.
>
> Selection.AutoFilter Field:=3D2, Criteria1:=3D">=3D" & Range("j18"), Op=
erator:=3DxlAnd,
> Criteria2:=3D"<" & Range("J19")
>
> Be sure that the stuff in the table and in your criteria cells are truly =
dates. If you
> remove all formatting (Edit - Clear - Formats), you should see numbers. =
Feb 1, 2007 is
> 39114. Etc.
>
> Say what does or doesn't happen when you run your code. Error messages? =
We're a bit in the
> dark.
>
> --
> Earl Kiosterudwww.smokeylake.com- Hide quoted text -
>
> - Show quoted text -

Earl, not sure what happened earlier, but thanks for it works fine
now.


Steve