macro's problem

[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


--
gerry405

[AlfD]

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

[Dave Peterson]

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

[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

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

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]

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

[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 Peterson Wrote:[color=blue]
> 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]

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