Firstly, excuse my ignorance in advance to those who make coding in VB look so easy!

Hi everyone

Can anyone show me how to filter on a column containing dates in the format of 21/04/59 being (21st April 1959), I set about recording a macro that first put a filter on the worksheet, then I selected the column with dates in and go to custom filter then select "equal to or lesser than" and key in the value 30/09/2005, but all that happens when I run this is I get blank lines ...

code below

Sub less_than()
'
' less_than Macro
' Macro recorded 07/10/2005 by SGUHT
'
' Keyboard Shortcut: Ctrl+k
'
Selection.AutoFilter
Range("U1").Select
Selection.AutoFilter Field:=21, Criteria1:="<=30/09/2005", Operator:= _
xlAnd
End Sub

Hi!

When Excel stores a date it does it as an integer counting from 1 Jan
1900 (=1) normally.

Your comparison needs to be done using similar numbers.

Put 30/09/2005 in a cell and it will look just like that.
Now reformat it as General and it will read 38625.
This gives you a simple way of "translating" between the number as
stored and the date as viewed.

That's fine on the worksheet but you need the integer form in the VBA.

Alf


--
AlfD
------------------------------------------------------------------------
AlfD's Profile: http://www.excelforum.com/member.php...fo&userid=4785
View this thread: http://www.excelforum.com/showthread...hreadid=474279

Dates can be a problem in VBA.

This may work:

Selection.AutoFilter Field:=21, Criteria1:="<="&clng(dateserial(2005,9,30))



gerry405 wrote:

Firstly, excuse my ignorance in advance to those who make coding in VB
look so easy!

Hi everyone

Can anyone show me how to filter on a column containing dates in the
format of 21/04/59 being (21st April 1959), I set about recording a
macro that first put a filter on the worksheet, then I selected the
column with dates in and go to custom filter then select "equal to or
lesser than" and key in the value 30/09/2005, but all that happens when
I run this is I get blank lines ...

code below

Sub less_than()
'
' less_than Macro
' Macro recorded 07/10/2005 by SGUHT
'
' Keyboard Shortcut: Ctrl+k
'
Selection.AutoFilter
Range("U1").Select
Selection.AutoFilter Field:=21, Criteria1:="<=30/09/2005",
Operator:= _
xlAnd
End Sub

--
gerry405

--

Dave Peterson

Dave,


thank you for your quick response, that code worked a treat...

Alf,

You answered another question that was bugging me, about
Dates/Formating...I thought at first I had a bug in EXCEL ...thanks


--
gerry405
------------------------------------------------------------------------
gerry405's Profile: http://www.excelforum.com/member.php...o&userid=27939
View this thread: http://www.excelforum.com/showthread...hreadid=474279

Dave

Intially, I thought that your posted code was working but it turns out that after checking it does not seem to do anything, could you? if you have time suggest someother way for me to sort on a date or even better still, having a prompt for inputing the date that need I need to sort on (as this changes from day to day)

Instead of trying this:
Selection.AutoFilter Field:=21, Criteria1:="<="&clng(dateserial(2005,9,30))
maybe you could use the same format as you see in the data:

Selection.AutoFilter Field:=21, _
Criteria1:="<="&format(dateserial(2005,9,30),"dd/mm/yyyy")


Excel/VBA doesn't always play nice with dates.



gerry405 wrote:

Dave

Intially, I thought that your posted code was working but it turns out
that after checking it does not seem to do anything, could you? if you
have time suggest someother way for me to sort on a date or even better
still, having a prompt for inputing the date that need I need to sort on
(as this changes from day to day)

gerry405 Wrote:
Dave,


thank you for your quick response, that code worked a treat...

Alf,

You answered another question that was bugging me, about
Dates/Formating...I thought at first I had a bug in EXCEL ...thanks


--
gerry405
------------------------------------------------------------------------
gerry405's Profile:
http://www.excelforum.com/member.php...o&userid=27939
View this thread:
http://www.excelforum.com/showthread...hreadid=474279

--
gerry405

--

Dave Peterson

Dave,

The code you suggested finally does the trick... thanks very much for that help!

also is there a way of prompting for a date as part of the macro, that way the user inputs the date (in the correct format) then the macro filter runs on based on what ever is input

Something like this??

Option Explicit
Sub testme01()

Dim myDate As Variant

myDate = InputBox(Prompt:="Enter a nicely formatted date")
If Trim(myDate) = "" Then
Exit Sub
End If

On Error Resume Next
myDate = CDate(myDate)
If Err.Number < 0 Then
MsgBox "Please enter a date"
Exit Sub
Err.Clear
End If

'just to show it worked
MsgBox Format(myDate, "mmmm dd, yyyy")

End Sub

====
But working with dates can be confusing.

If I type 01/02/03, how will you ever be sure that you get the date that I
meant?

An alternative...

Maybe use a calendar control.
Ron de Bruin has some tips/links at:
http://www.rondebruin.nl/calendar.htm


gerry405 wrote:[color=blue]

Dave,

The code you suggested finally does the trick... thanks very much for
that help!

also is there a way of prompting for a date as part of the macro, that
way the user inputs the date (in the correct format) then the macro
filter runs on based on what ever is input

Dave Peterson Wrote:
Instead of trying this:
Selection.AutoFilter Field:=21,
Criteria1:="="&clng(dateserial(2005,9,30))
maybe you could use the same format as you see in the data:

Selection.AutoFilter Field:=21, _
Criteria1:="="&format(dateserial(2005,9,30),"dd/mm/yyyy")


Excel/VBA doesn't always play nice with dates.

gerry405 wrote:

Dave

Intially, I thought that your posted code was working but it turns
out
that after checking it does not seem to do anything, could you? if
you
have time suggest someother way for me to sort on a date or even
better
still, having a prompt for inputing the date that need I need to sort
on
(as this changes from day to day)


Dave Peterson

--
gerry405

--

Dave Peterson