Closed Thread
Results 1 to 8 of 8

Way to have VB code to filter on entries that have cell with today's date?

  1. #1
    StargateFan
    Guest

    Way to have VB code to filter on entries that have cell with today's date?

    How would one do that, anyone know? If I record the macro manually, I
    of course just get a specific date only. Thanks.


  2. #2
    Bob Phillips
    Guest

    Re: Way to have VB code to filter on entries that have cell with today's date?

    In your recorded macro, change the date to the word Date, that is VBA for
    today's date.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
    news:[email protected]...
    > How would one do that, anyone know? If I record the macro manually, I
    > of course just get a specific date only. Thanks.
    >




  3. #3
    StargateFan
    Guest

    Re: Way to have VB code to filter on entries that have cell with today's date?

    On Tue, 29 Mar 2005 13:18:29 +0100, "Bob Phillips"
    <[email protected]> wrote:

    >In your recorded macro, change the date to the word Date, that is VBA for
    >today's date.


    I must have done something wrong.

    I tried that this morning and my original recorded macro looks like
    this:

    Selection.AutoFilter Field:=11, Criteria1:="2005.05.06.Fri"

    I then changed the actual date to the word "Date" so that macro looks
    like this:

    Selection.AutoFilter Field:=11, Criteria1:="Date"

    I get a filtered result of zero. No records show up even there are 3
    with today's date.

    I removed the customized date format to Excel's regular one and still
    this happened. What did I do wrong, pls?

    Tx. :oD


  4. #4
    Tom Ogilvy
    Guest

    Re: Way to have VB code to filter on entries that have cell with today's date?

    don't put it in double quotes

    Selection.AutoFilter Field:=11, Criteria1:=Date

    --
    Regards,
    Tom Ogilvy


    "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
    news:[email protected]...
    > On Tue, 29 Mar 2005 13:18:29 +0100, "Bob Phillips"
    > <[email protected]> wrote:
    >
    > >In your recorded macro, change the date to the word Date, that is VBA for
    > >today's date.

    >
    > I must have done something wrong.
    >
    > I tried that this morning and my original recorded macro looks like
    > this:
    >
    > Selection.AutoFilter Field:=11, Criteria1:="2005.05.06.Fri"
    >
    > I then changed the actual date to the word "Date" so that macro looks
    > like this:
    >
    > Selection.AutoFilter Field:=11, Criteria1:="Date"
    >
    > I get a filtered result of zero. No records show up even there are 3
    > with today's date.
    >
    > I removed the customized date format to Excel's regular one and still
    > this happened. What did I do wrong, pls?
    >
    > Tx. :oD
    >




  5. #5
    StargateFan
    Guest

    Re: Way to have VB code to filter on entries that have cell with today's date?

    On Fri, 6 May 2005 07:56:56 -0400, "Tom Ogilvy" <[email protected]>
    wrote:

    >don't put it in double quotes
    >
    >Selection.AutoFilter Field:=11, Criteria1:=Date
    >
    >--
    >Regards,
    >Tom Ogilvy


    Woops. Thanks!

    >"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
    >news:[email protected]...
    >> On Tue, 29 Mar 2005 13:18:29 +0100, "Bob Phillips"
    >> <[email protected]> wrote:
    >>
    >> >In your recorded macro, change the date to the word Date, that is VBA for
    >> >today's date.

    >>
    >> I must have done something wrong.
    >>
    >> I tried that this morning and my original recorded macro looks like
    >> this:
    >>
    >> Selection.AutoFilter Field:=11, Criteria1:="2005.05.06.Fri"
    >>
    >> I then changed the actual date to the word "Date" so that macro looks
    >> like this:
    >>
    >> Selection.AutoFilter Field:=11, Criteria1:="Date"
    >>
    >> I get a filtered result of zero. No records show up even there are 3
    >> with today's date.
    >>
    >> I removed the customized date format to Excel's regular one and still
    >> this happened. What did I do wrong, pls?
    >>
    >> Tx. :oD
    >>

    >



  6. #6
    StargateFan
    Guest

    Re: Way to have VB code to filter on entries that have cell with today's date?

    On Sat, 14 May 2005 02:34:35 -0400, StargateFan
    <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote:

    >On Fri, 6 May 2005 07:56:56 -0400, "Tom Ogilvy" <[email protected]>
    >wrote:
    >
    >>don't put it in double quotes
    >>
    >>Selection.AutoFilter Field:=11, Criteria1:=Date


    Tom, hi. This didn't work and I'm wondering why. The macro looks
    like this now:

    Sub TestDueTODAY()
    ' Macro recorded 5/14/2005 by .
    '
    Selection.AutoFilter Field:=10, Criteria1:="<>"
    Selection.AutoFilter Field:=10, Criteria1:=Date
    End Sub

    Does the difficulty lie with the formatting of the date? Although I
    enter with ^+; to get today's date, the cell format is in
    ddd.mmm.dd.yyyy so it's easier for the user to read. Does the problem
    lie there?

    I get values, of course, with the <> filter entry. I've tried the
    above macro with just each line and the <> works fine. The Date one
    doesn't. (p.s., the column here is a different one that the trial I
    was using before. I have the actual file with me now and that's why
    it's a 10 here instead of an 11 like before, but that part's okay).

    Tx.

    >
    >Woops. Thanks!
    >
    >>"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
    >>news:[email protected]...
    >>> On Tue, 29 Mar 2005 13:18:29 +0100, "Bob Phillips"
    >>> <[email protected]> wrote:
    >>>
    >>> >In your recorded macro, change the date to the word Date, that is VBA for
    >>> >today's date.
    >>>
    >>> I must have done something wrong.
    >>>
    >>> I tried that this morning and my original recorded macro looks like
    >>> this:
    >>>
    >>> Selection.AutoFilter Field:=11, Criteria1:="2005.05.06.Fri"
    >>>
    >>> I then changed the actual date to the word "Date" so that macro looks
    >>> like this:
    >>>
    >>> Selection.AutoFilter Field:=11, Criteria1:="Date"
    >>>
    >>> I get a filtered result of zero. No records show up even there are 3
    >>> with today's date.
    >>>
    >>> I removed the customized date format to Excel's regular one and still
    >>> this happened. What did I do wrong, pls?
    >>>
    >>> Tx. :oD



  7. #7
    Dave Peterson
    Guest

    Re: Way to have VB code to filter on entries that have cell with today'sdate?

    Dates, VBA and autofilter don't always work nice.

    But since you know the format of the cell, this might work ok for you:

    Selection.AutoFilter Field:=10, Criteria1:=Format(Date, "ddd.mmm.dd.yyyy")

    (It worked for me.)

    StargateFan wrote:
    >
    > On Sat, 14 May 2005 02:34:35 -0400, StargateFan
    > <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote:
    >
    > >On Fri, 6 May 2005 07:56:56 -0400, "Tom Ogilvy" <[email protected]>
    > >wrote:
    > >
    > >>don't put it in double quotes
    > >>
    > >>Selection.AutoFilter Field:=11, Criteria1:=Date

    >
    > Tom, hi. This didn't work and I'm wondering why. The macro looks
    > like this now:
    >
    > Sub TestDueTODAY()
    > ' Macro recorded 5/14/2005 by .
    > '
    > Selection.AutoFilter Field:=10, Criteria1:="<>"
    > Selection.AutoFilter Field:=10, Criteria1:=Date
    > End Sub
    >
    > Does the difficulty lie with the formatting of the date? Although I
    > enter with ^+; to get today's date, the cell format is in
    > ddd.mmm.dd.yyyy so it's easier for the user to read. Does the problem
    > lie there?
    >
    > I get values, of course, with the <> filter entry. I've tried the
    > above macro with just each line and the <> works fine. The Date one
    > doesn't. (p.s., the column here is a different one that the trial I
    > was using before. I have the actual file with me now and that's why
    > it's a 10 here instead of an 11 like before, but that part's okay).
    >
    > Tx.
    >
    > >
    > >Woops. Thanks!
    > >
    > >>"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
    > >>news:[email protected]...
    > >>> On Tue, 29 Mar 2005 13:18:29 +0100, "Bob Phillips"
    > >>> <[email protected]> wrote:
    > >>>
    > >>> >In your recorded macro, change the date to the word Date, that is VBA for
    > >>> >today's date.
    > >>>
    > >>> I must have done something wrong.
    > >>>
    > >>> I tried that this morning and my original recorded macro looks like
    > >>> this:
    > >>>
    > >>> Selection.AutoFilter Field:=11, Criteria1:="2005.05.06.Fri"
    > >>>
    > >>> I then changed the actual date to the word "Date" so that macro looks
    > >>> like this:
    > >>>
    > >>> Selection.AutoFilter Field:=11, Criteria1:="Date"
    > >>>
    > >>> I get a filtered result of zero. No records show up even there are 3
    > >>> with today's date.
    > >>>
    > >>> I removed the customized date format to Excel's regular one and still
    > >>> this happened. What did I do wrong, pls?
    > >>>
    > >>> Tx. :oD


    --

    Dave Peterson

  8. #8
    StargateFan
    Guest

    Re: Way to have VB code to filter on entries that have cell with today's date?

    On Mon, 16 May 2005 07:52:21 -0400, StargateFan
    <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote:

    >On Sat, 14 May 2005 02:34:35 -0400, StargateFan
    ><IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote:
    >
    >>On Fri, 6 May 2005 07:56:56 -0400, "Tom Ogilvy" <[email protected]>
    >>wrote:
    >>
    >>>don't put it in double quotes
    >>>
    >>>Selection.AutoFilter Field:=11, Criteria1:=Date

    >
    >Tom, hi. This didn't work and I'm wondering why. The macro looks
    >like this now:
    >
    >Sub TestDueTODAY()
    >' Macro recorded 5/14/2005 by .
    >'
    > Selection.AutoFilter Field:=10, Criteria1:="<>"
    > Selection.AutoFilter Field:=10, Criteria1:=Date
    >End Sub
    >
    >Does the difficulty lie with the formatting of the date? Although I
    >enter with ^+; to get today's date, the cell format is in
    >ddd.mmm.dd.yyyy so it's easier for the user to read. Does the problem
    >lie there?
    >
    >I get values, of course, with the <> filter entry. I've tried the
    >above macro with just each line and the <> works fine. The Date one
    >doesn't. (p.s., the column here is a different one that the trial I
    >was using before. I have the actual file with me now and that's why
    >it's a 10 here instead of an 11 like before, but that part's okay).
    >
    >Tx.


    [snip]

    Was wondering if anyone knew why the Date feature wouldn't bring up
    entries with today's date. There are entries there, I make sure to
    add some in the test file. Tx.


Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1