Auto filter Query??

[K1KKKA]

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]

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


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 Kiosterud]

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")

 

[K1KKKA]

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






- 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]

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)

 

[Don Guillett]

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

 

[Earl Kiosterud]

-----------------------------------------------------------------------

Thanks Earl,

Have renamed the relevant cells as Date1 and Date2

Am trying to get the range to recognise the following,
< 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.

 

[K1KKKA]

Steve,

This code works for me. I get the records between the dates specified inJ18 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, not sure what happened earlier, but thanks for it works fine
now.


Steve