Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2001
    Location
    QLD, Australia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering Dates (Excel 97)

    Frustration has set it ! I'm sure I'm missing something simple here....
    I am trying to record a macro to filter a list. I want an Input box to prompt for the criteria from the user. Excel will then filter the list based on what was entered. I can get this working well with text-formatted cells, but I need this to work with dates - and XL just won't do it.
    Any ideas ? I have attached my working sample of looking up text. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Dates (Excel 97)

    This works for me:

    <pre>Public Sub AFDate()
    Dim strDate As String
    strDate = InputBox("Enter date")
    ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:=strDate
    End Sub
    </pre>

    Legare Coleman

  3. #3
    New Lounger
    Join Date
    May 2001
    Location
    QLD, Australia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Dates (Excel 97)

    It's a no-go for me. I tried exactly the same fix, but it still returns nothing. I've tried typing in various date formats in the inputbox, all with no success.
    Thanks, anyway.
    Craig

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Dates (Excel 97)

    I have attached your workbook with my macro copied from my previous message and pasted in. It works correctly for me on Excel 97/SR2. If this does not work for you, then what SR level is your Excel 97?
    Attached Files Attached Files
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    May 2001
    Location
    QLD, Australia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Dates (Excel 97)

    This is crazy really. I am your attached file with your macro, and this is what I get. Watch the video !
    Perhaps I have an obscure option set somewhere in Excel ? (I am running SR2).
    Attached Files Attached Files

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Dates (Excel 97)

    The date you use in the criteria has to have exactly the same format as the DISPLAYED date in the column being filtered. There are three possible solutions to your problem:

    1- Enter the date a 2/1/01 (not 2001)
    2- Change the column format to m/d/yyyy
    3- Change the macro to:

    <pre>Public Sub AFDate()
    Dim strDate As String
    strDate = InputBox("Enter date")
    strDate = Format(DateValue(strDate), "m/d/yy")
    ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:=strDate
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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