excel date format problem

T

Tim

Dear All,

i'm trying to set a custom autofilter using VBA/inputboxes. everything
seems to work BUT I am trying to filter dates taken from inputboxes and in
the process, the days and months are being transposed... eg, in the
inputbox, i entered '1/6' meaning '1st June 2008' but the
autofilter is showing '6th January 2008'... obviously returning the wrong
result.

any ideas how to fix that?! I'm using the following syntax to set & use the
date variable: -
==
Dim rng1, rng2
rng1 = Format(CDate(InputBox("from")), "dd/mm/yyyy")
rng2 = Format(CDate(InputBox("to")), "dd/mm/yyyy")
Selection.AutoFilter Field:=3, Criteria1:="<=" & rng1, Operator:=xlAnd,
Criteria2:=">=" & rng2
===

tia,

Tim
 
B

Bob Phillips

Try this

Dim rng1, rng2
With Selection
rng1 = Format(CDate(InputBox("from")), .Cells(1,1).Numberformat)
rng2 = Format(CDate(InputBox("to")), .Cells(1,1).Numberformat)
.AutoFilter Field:=3, Criteria1:="<=" & rng1,
Operator:=xlAnd,Criteria2:=">=" & rng2
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to use variable that should be in "quotes" 4
Autofilter in a macro 1
Adding a button 1
Criteria Macro 1
Help with InputBox in a Macro 3
Problem with date formatting 5
Filter Problem 2
Match Date Search 1

Top