how to use variable that should be in "quotes"

[Tim]

Hi,

I have a small piece of VBA which is used to filter a list (using a custom
autofilter) between 2 sets of dates from input boxes (code below). my
problem is that it doesn't filter the list properly (hides all the data). i
think its because the criteria (rng1 & rng2) in the autofilter should be in
quotes, but i don't know how to do that... i can't work out how to make
another variable which is a concatenation of " & rng1 & "

could someone please help me (or tell me an easier way to aceive the same
thing!)?

Thanks,

Tim
======
Sub ChooseDates()
Dim rng1 As Date, rng2 As Date

rng1 = InputBox("from")
rng2 = InputBox("to")

Selection.AutoFilter Field:=3, Criteria1:=rng1, Operator:=xlAnd,
Criteria2:=rng2
End Sub

 

[Jim Thomlinson]

Not sure what your date data looks like but more likely has to do with input
boxes returning text and your data being dates. Is that the case??? If so we
we can use Cdate to convert the text to a date...

 

[Tim]

Hi Jim,

Thanks for the suggestion, but it gave the same result - i'm guessing you
mean i should use CDate like this : -

rng1 = cdate(InputBox("from"))

the table being filtered is a mixture of string, date, integer columns; the
one i'm trying to filter on is full of dates. if i put a message box in, it
is recognising my inputs as dates (eg, if i enter 1/1, the messge box
returns 01/01/2008) and they are exactly the same format as in the table.

 

[Jim Thomlinson]

Sorry I was kind of leading you down the wrong path. The purpose to
converting to date is that since you can not guranatee the format of the
incoming date you can convert to date and then format as necessary...

Sub ChooseDates()
Dim strStartDate As string, strEndDate As string

strStartDate = format(cdate(InputBox("from")), "mm/dd/yyyy")
strEndDate = format(cdate(InputBox("to")), "mm/dd/yyyy")

Selection.AutoFilter Field:=3, Criteria1:=">=" & strStartDate ,
Operator:=xlAnd,
Criteria2:="<=" & strEndDate
End Sub

 

[Tim]

Thanks again Jim.

I still doesn't work, but now i know why (still don't know how to fix it
though!)

i tried manually custom filtering and it displayed what the VBA was trying
to do... in the inputbox, i entered '1/6' meaning '1st June 2008' but the
autofilter is showing '6th January 2008'

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

cheers,

tim