macro's problem

G

gerry405

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
 
A

AlfD

Hi!

When Excel stores a date it does it as an integer counting from 1 Ja
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 a
stored and the date as viewed.

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

Al
 
D

Dave Peterson

Dates can be a problem in VBA.

This may work:

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

gerry405

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
 
G

gerry405

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)
 
D

Dave Peterson

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.
 
G

gerry405

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 said:
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.
Click to expand...
 
D

Dave Peterson

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

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
Click to expand...
 
Top