Excel VBA autofilter problem

[tudorpe]

I have created this code:

Sub Macro1()

Dim mydate1 As Date
Dim mydate2 As Date

mydate1 = Range("p12")
mydate2 = Range("q12")
Sheets("Records").Select
Range("a1").Select
Selection.AutoFilter Field:=1, Criteria1:=">" & mydate1
Operator:=xlAnd _
, Criteria2:="<" & mydate2

End Sub

what it does is filter a group of records i have by their date, the
dates are obtained from p12 & p13 which in turn are CONCATENATEs of th
three cells above, these cells are validated cells which allow the use
to input
1-31 (the day) in the first cell
1-12 (the month) in the second cell
and 2004-2010 (the year) in the final cell.
In between each number is a "/" the cells P12 & Q12 are both formate
"dd/mm/yyyy".

The problem is, when the macro is run ALL entries in the database ar
filtered out, but when i check it by clicking on "custom" in th
autofilter box the date is correct and when i click OK to exit, th
correct records are displayed!!!

I'm stumped! - any help would be appreciate

 

[Dave Peterson]

Working with dates in filters in VBA can be a challenge.

The first thing I'd do is to modify those formulas in the worksheet. Instead of
just concatenating the strings together, I'd use:

=date(a1,b1,c1)
(and format it nicely as a date (with slashes).)

then try your code. If that doesn't work, try:

Selection.AutoFilter Field:=1, Criteria1:=">" & clng(mydate1), Operator:=xlAnd,
_
Criteria2:="<" & clng(mydate2)

sometimes looking at the value of the date (like formatting a date cell as
general) makes things work.

 

[tudorpe]

Thanks for that, the code works much better now, unfortunatley i stil
have the same problem, the code runs fine with no errors and when
check the custom menu on the autofiltered range the two dates chosen b
the user are displayed correctly but the results of the filter aren'
shown in the database, to show them i have to go into the custom men
and click OK, they then show.

Any thoughts

 

[Dave Peterson]

And you changed the concatenation to =date() so that there would be a real date
in those cells--not just text that looks like a date???

If you didn't include that in your test, try that first.

If you did, then depending on what you're doing with the filtered data, maybe
you could change the numberformat to general, filter based on just the date's
serial number, do your processing, show all the data and then reset the
numberformat.

I put a bunch of dates in column E. Then I filtered on A:K.

Option Explicit
Sub testme()
Dim Rng As Range
Dim RngF As Range
Dim myNumberFormat As String

With ActiveSheet
Set Rng = Intersect(.AutoFilter.Range, .Range("e:e"))
Set RngF = Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1, 1)

myNumberFormat = Rng.Offset(1, 0).Resize(1, 1).NumberFormat
RngF.NumberFormat = "general"
Rng.AutoFilter field:=5, _
Criteria1:=CLng(DateSerial(2004, 6, 21))

'do lots of processing here

If .AutoFilterMode Then
.ShowAllData
End If
RngF.NumberFormat = myNumberFormat
End With

End Sub

But this doesn't help if you're filtering and giving back control to the user.

Maybe something like this near the end that resets the numberformat for those
hidden cells, too:

For Each myCell In RngF.Cells
myCell.NumberFormat = myNumberFormat
Next myCell


But you did change those formulas to =date(), right?

 

[tudorpe]

Thanks I've now got it working,

The formula in the two cells was =DATE(P10,P9,P8)
and =DATE(Q10,Q9,Q8) but what i did was create a formula below whic
was exactly the same but was in general format (i did this so the use
could see what date he put in, in the cell above but i used this cel
with the general number format as the input to the VBA code), the cod
now looks like this:

Dim mydate1 As String
Dim mydate2 As String

mydate1 = Range("p12")
mydate2 = Range("q12")

Sheets("Records").Select
Sheets("Records").Unprotect
Range("A:A").Select
Selection.NumberFormat = "General"
Range("a1").Select
Selection.AutoFilter Field:=1, Criteria1:=">" & mydate1
Operator:=xlAnd _
, Criteria2:="<" & mydate2
Range("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
Range("a1").Select

Thanks for your hel

 

[Dave Peterson]

When you unfiltered, didn't you have the dates in column A shown as General--not
as dates?

 

[tudorpe]

yes, but thats not a problem as the users won't be interested in th
filtered out rows.

They only need to see what they have requested and so far it work
every time