Using the greater than feature in Autofilter

[Navvy]

Hello!

I am trying to use the autofilter with variables. Here's my code:

Selection.AutoFilter Field:=7, Criteria1:=">=low", Operator:=xlAnd _
, Criteria2:=&quot;<=high&quot;

low and high have values, but I am not sure where to put the &quot;&quot;. I tried taking them out but I get a syntax error.

 

[xlbo]

Selection.AutoFilter Field:=7, Criteria1:=&quot;>=&quot; & low , Operator:=xlAnd _
, Criteria2:=&quot;<=&quot; & high


Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[Navvy]

Geoff, Perfect! Thank you.

However, for some reason it seems to convert my date into American style, even though I put it as English. What can I do?

 

[xlbo]

shouldn't really matter as it's just a case of formats - it's the underlying value that is used
If it is a problem tho, post your code and I'll have a look

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[Navvy]

Dim inDate As Date, low As Date, high As Date
inDate = mydate.Value
low = inDate - 1
high = inDate + 1

Selection.AutoFilter Field:=7, Criteria1:=&quot;>=&quot; & low, Operator:=xlAnd _
, Criteria2:=&quot;<=&quot; & high

 

[xlbo]

ok - this is because VBA is native US for dates, you need to use:

Dim inDate As Date, low As Date, high As Date
inDate = mydate.Value
low = format(inDate - 1,&quot;dd/mm/yyyy&quot
high = format(inDate + 1,&quot;dd/mm/yyyy&quot

Selection.AutoFilter Field:=7, Criteria1:=&quot;>=&quot; & low, Operator:=xlAnd _
, Criteria2:=&quot;<=&quot; & high



Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[Navvy]

Would have thought that would work, but no luck Geoff.

 

[xlbo]

What's the actual problem you are having ??
you could try:
Dim inDate As Date, low as long, high As long
inDate = mydate.Value
low = inDate - 1
high = inDate + 1

Selection.AutoFilter Field:=7, Criteria1:=&quot;>=&quot; & low, Operator:=xlAnd _
, Criteria2:=&quot;<=&quot; & high

Where it is just using the date VALUE

or, assuming your dates are in col G, then

Dim inDate As Date, low As Date, high As Date
inDate = mydate.Value
low = format(inDate - 1,range(&quot;G2&quot.numberformat)
high = format(inDate + 1,range(&quot;G2&quot.numberformat)

Selection.AutoFilter Field:=7, Criteria1:=&quot;>=&quot; & low, Operator:=xlAnd _
, Criteria2:=&quot;<=&quot; & high

to pick up the EXACT number format being used in the cells which contain your dates

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244

 

[Navvy]

Amazing! Thank You Geoff!

 

[xlbo]

Which did you use in the end ??

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244