Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date filter criteria not being applied

    I am trying to create vba code which will automatically filter on a specific currency = 'GBP' (column 16) and where the maturity date (column 7) is less then today's date. The code does the filtering for the currency but not for the date criteria. Am at a point where I am just spinning my wheels. Hoping someone can assist in determining what I am doing wrong. Thank you for your time in advance.

    Code:
     Range("A1:Q1").Select
        Selection.AutoFilter
        
        ActiveSheet.Range("$A$1:$Q$1000000").AutoFilter Field:=16, Criteria1:="GBP"
        ActiveSheet.Range("$A$1:$Q$1000000").AutoFilter Field:=7, Criteria1:= _
         "<" & Now(), Operator:=xlAnd

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Cirugio,

    Try this:

    Code:
    Range("A1:Q1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$Q$1000000").AutoFilter Field:=7, Criteria1:= _
        "<" & Now, Operator:=xlAnd
    ActiveSheet.Range("$A$1:$Q$1000000").AutoFilter Field:=16, Criteria1:="GBP"
    I think you need to reverse the order with the "And" operator first and remove the () for the Now keyword.

    HTH,
    Maud

  3. #3
    New Lounger
    Join Date
    May 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maud,

    I tried but still didn't work. I was able to get it going after more surfing on the net. This ended up being my solution in case your interested: Thanks for your time.
    With ActiveSheet.Range("A1:Q" & Cells(Rows.Count, "A").End(xlUp).Row)
    .AutoFilter
    .AutoFilter Field:=16, Criteria1:="USD"
    .AutoFilter Field:=7, Criteria1:="<" & CLng(Date)
    End With






    Quote Originally Posted by Maudibe View Post
    Cirugio,

    Try this:

    Code:
    Range("A1:Q1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$Q$1000000").AutoFilter Field:=7, Criteria1:= _
        "<" & Now, Operator:=xlAnd
    ActiveSheet.Range("$A$1:$Q$1000000").AutoFilter Field:=16, Criteria1:="GBP"
    I think you need to reverse the order with the "And" operator first and remove the () for the Now keyword.

    HTH,
    Maud
    Last edited by cirugio; 2015-06-09 at 11:59.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Thanks for the follow-up

Posting Permissions

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