Autofilter and VBA

[Ronald Janssen]

Hi,
I use a Excel for a small database (less than 1000 records). One of my
columns contains dates. Using Autofilter I can select a date and get the
records containing this date.

My problem starts with Autofilter as part of a macro/vba sub

Using the same filter criteria but this time automated the corresponding
number of records are zero !

If I then check my database manually:
The Autofilter button for the date column is colored blue as it should be.
If I click on that button I notice that the correct date in the list is
selected/highlighted but it requires a "manual" click to actually get the
data visible.

This "problem" occurs both in 97 as well as in XP.

The help file does not "help" nor have I found an answer in Microsoft's KB.

Anyone familiar with this phenomena and its solution ?

Thanks for your reply
Ronald

 

[Peo Sjoblom]

Could you post the code?

 

[Debra Dalgleish]

Try converting the dates to serial numbers. For example, to prompt for
dates, and filter on column A:

Dim strStart As String
Dim strEnd As String
strStart = Application.InputBox("Start Date")
strEnd = Application.InputBox("End Date")

Selection.AutoFilter Field:=1, _
Criteria1:=">=" & CLng(CDate(strStart)), Operator:=xlAnd, _
Criteria2:="<=" & CLng(CDate(strEnd))

 

[Ronald Janssen]

Debra,

Thanks, you helped me solve my problem. It was due to the way I passed on
the date.
Your suggestion did not work immediately, I had to add a variable with date
properties to get it working
My rough code looked like this

Dim strDatum as string
Dim datDatum as date
strDatum= Application.Inputbox etcetera
datDatum=Clng(Cdate(strDatum))

Using variable datDatum as criteria1 in Autofilter gave the correct result.

Thanks again !

Ronald