PDA

View Full Version : Solved: Problem with Autifilter Date Range



tlchan
01-01-2009, 01:25 AM
I am having problem of autofilter the data for date range between date value under cell M1 and N1 input via textbox in userform.

The date appeared in cell M1 & N1 does not same as in textbox input and the autofilter date does not filter the date as expected.

Please help me out. :rofl:

Artik
01-01-2009, 09:25 AM
I think, that dates in columns L are no dates. They are the text. They only look like dates. There is effect of import of data from other base (probably).
Before You run [Serch Date], you must "translate" text on dates.
1. Select data in column L.
2. From menu select Data/Text to columns... (sorry, I have polish version) :) )
http://gfx.efotek.pl/images/tlyfvbdx3rl0ebp20p9_thumb.gif (http://efotek.pl/viewer.php?file=tlyfvbdx3rl0ebp20p9.gif)
3. In first step select "divided" and no [Next], but [End]
http://gfx.efotek.pl/images/uajullv2utbsnwgql14x_thumb.gif (http://efotek.pl/viewer.php?file=uajullv2utbsnwgql14x.gif)

Several errors are in procedure cmdSearch_Click.
My proposal:Sub cmdSearch_Click()
Dim dDatestart As Date
Dim dDatelast As Date
Dim ldatelast As Long
Dim lDatestart As Long

With Sheets("wdnsse")
.AutoFilterMode = False
.Cells(1, 13).Value = frmsearch.tbstartdate.Value
.Cells(1, 14).Value = frmsearch.tbenddate.Value
dDatestart = .Range("M1")
dDatelast = .Range("N1")
lDatestart = DateSerial(Year(dDatestart), Month(dDatestart), Day(dDatestart))
ldatelast = DateSerial(Year(dDatelast), Month(dDatelast), Day(dDatelast))
.Range("L2").AutoFilter Field:=12, _
Criteria1:=">=" & lDatestart, _
Operator:=xlAnd, _
Criteria2:="<=" & ldatelast
End With
End SubThis procedure is not optimum and no error handling.

Artik

tlchan
01-02-2009, 04:57 PM
Thanks Artik for your solution, its working fine now.

Appreciate your assistance.


Thank you:hi: