#1. Your guess is as good as mine why they don't play nice. I remember one
time, I had to change the data's format to General, then filter using the serial
date number.
Sometimes (once??), I picked up the format from the first cell in that column
and used that to filter. The data all had the same format, so I was happy.
#2. VBA has its own =isdate() function that you could use.
broro183 wrote:
Hi Dave,
Sorry about "piggybacking" this thread but I just noticed your comment
"Dates and filtering and VBA don't always play nice...". just curious
but why is this?
I do a lot of filtering on ageing inventory (cheese) & have created a
macro with a keyboard shortcut to allow me to filter faster than I can
by using the mouse & "custom filter" etc. I have found that when
filtering for dates the macro sometimes works & sometimes doesn't & am
unable to figure out what causes the differences b/n date/string
recognition. I'm using Excel 2002 both @ home & @ work; my home
computer has not yet failed to filter dates but the work one has. There
is no discernible difference (to me anyway) b/n the formats of cells
that work & cells that don't, or if the cell values are "real" dates
(eg dd/mm/yy) or "built" from referenced codes (eg = A1 & "/" & B1 &
"/" & C1).
I'm going to try your coding below when I get to work tomorrow &
hopefully it will solve any future issues.
Question 2: I use this macro for much more than just date filtering so
I'd like to be able to check if it is a date I'm filtering. How can I
check the formatting of the active cell & see if it is a date before
your line of code?
eg
Dim FilterValue As String
Dim FilterValueDate As Date
FilterValue = ActiveCell
If ActiveCell.Format = "m/d/yyyy" Then 'doesn't work
FilterValueDate = FilterValue
Else
End If
MsgBox ActiveCell.Format ' doesn't work
Thanks in advance,
Cheers
Rob Brockett
NZ
Always learning & the best way to learn is to experience...
Dave Peterson Wrote:
Dates and filtering and VBA don't always play nice...
selection.autoFilter Field:=28,
Criteria1:="<"&clng(cdate(variablestr1)), _
Operator:=xlOr, Criteria2:=""&clng(cdate(variablestr2))
is where I'd start.
I figured the variables were strings that looked liked dates.
Dave Peterson
--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=358973
--
Dave Peterson