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

    Autofilter macro (Excel 97)

    Hi all,
    I am having dreadful problems trying to get a macro to work properly. The macro accepts 2 dates via inputboxes, then looks up an autofilter to return records between the two dates. I can get it to work looking for one specific date (Thanks to Legaire's help), but I just cannot get it to work with 2 dates.
    I have attached the file. Any thoughts, help would be appreaciated from Down Under !
    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: Autofilter macro (Excel 97)

    See if this works any better.

    <pre>Private Sub cmdTwoDates_Click()
    Dim strStartDate As String
    Dim strEndDate As String
    Dim FirstDate As Date ' Declare variables.
    Dim SecondDate As Date
    Dim Interval As String
    Dim NumMonths As Integer

    Interval = "m"
    NumMonths = -12

    strStartDate = InputBox("Enter Start Date of Review")
    FirstDate = strStartDate
    ' firstdate is required because the dateadd function requires a variable with a date format
    ' whereas the autofilter command below requires a string value(strDate)
    strStartDate = DateAdd(Interval, NumMonths, FirstDate)
    strStartDate = Format(DateValue(strStartDate), "m/d/yyyy")

    strEndDate = InputBox("Enter End Date of Review")
    SecondDate = strEndDate
    ' firstdate is required because the dateadd function requires a variable with a date format
    ' whereas the autofilter command below requires a string value(strDate)
    strEndDate = DateAdd(Interval, NumMonths, SecondDate)
    strEndDate = Format(DateValue(strEndDate), "m/d/yyyy")



    Range("a4").Select

    ActiveSheet.Range("A4").AutoFilter Field:=7, Criteria1:=">=" & strStartDate, Operator:=xlAnd, _
    Criteria2:="<=" & strEndDate

    End Sub
    </pre>

    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Aug 2001
    Location
    Johannesburg, South Africa
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter macro (Excel 97)

    Hi Legare,
    have you tested your code?
    It's quite an interesting problem and I tried to make it work.
    However I found that there are a number of problems:
    a) the data in col F (date drafted) and G (date endorsed) must be entered as strings, i.e. with a ' in front, which is quite cumbersome. If they entered as normal dates even the filtering for a single date does not work (for me, on Excel 2k)
    [img]/forums/images/smilies/cool.gif[/img] code for Criteria1: using "<=" or ">=" leads to '0 records found' . I get a result only if I use "=" or "<>". However this seems to be particular to the strings resembling a date format "dd/mm/yyyy" or similar. I found no problem in filtering lists (2 arguments) that are purely text.
    c) I also noted that, if I sort strings that resemble dates (i.e. "mm/dd/yyyy") the sorting does not really work as it would with dates and thus the "<=" or ">=" operation won't work.
    Any further suggestions?

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

    Re: Autofilter macro (Excel 97)

    Yes, I did test that code and it works just fine in the sheet that Craig posted using XL 2K. The dates in that sheet are not entered as strings. Did you try it in his sheet?

    There was a bug in the one date code that was causing it not to work. I didn't look at that code since Craig indicate that it worked and was asking about the two date code.

    Did you notice that the code is subtracting 12 months from the dates entered before using them for the filter?

    In the attached sheet, try entering 3/16/2002 for the single date and 3/1/2002 and 3/30/2002 for the two dates and see what you get.
    Attached Files Attached Files
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Aug 2001
    Location
    Johannesburg, South Africa
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter macro (Excel 97)

    Hi Legare,
    Thanx for your reply.
    Very strange indeed. i need to change the format from /yyyy to /yy to make the single-date work. The double-date works fine.
    I have to play around with this a lot more, as I normally use dd/mm/yyyy formatting (but for this test I changed the settings in the Control panel to US-english)
    I will report later if I get it to work with both buttons.
    Thanx again.

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

    Re: Autofilter macro (Excel 97)

    The format of the date used in the criteria must exactly match the display format of the date on the sheet. If you are displaying /yy then the critera must use /yy.
    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
  •