[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Adding an additional filter to an Autofilter

Posted on 2011-09-15
6
Medium Priority
?
274 Views
Last Modified: 2012-06-27
I am using the following code to extract data and need to add another filter.  It is to filter anything with a date of yesterday. (today -1)  Need to get all lines that are < today minus one.

See code below line 24.  How would I add this additional filter to column "T"?  -R-
Sub MDL_GetCompletedOrdersTodayMinusOneDay()
'On Format tab only
'Working on sending this total to the Dashboard too.

Dim rgFilter As Range, rgTarg As Range
Set rgTarg = Worksheets("MDL - Summary").Range("D8")  'Record count goes here
Sheets("Format").Select

With Worksheets("Format")       'Worksheet to be filtered
    Set rgFilter = .Range("A1")     'First header label in range to be filtered
    Set rgFilter = Intersect(.UsedRange, rgFilter.Resize(1, 100).EntireColumn)   'All the rows of data
    
    rgTarg.Formula = "=SUBTOTAL(3,'" & .Name & "'!" & rgFilter.Columns(3).Address & ")-1"
    With rgFilter
        .AutoFilter
        .AutoFilter Field:=7, Criteria1:="SPCLMDL"
        
        .AutoFilter Field:=10, Criteria1:="=CNF  LKD  REL", Operator:=xlOr, Criteria2:="=CNF  REL"
        .AutoFilter Field:=22, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Date - 1, "m/d/yyyy")) 'Today minus one
'        .AutoFilter Field:=22, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Date, "m/d/yyyy")) 'just today
                
        .Sort Key1:=Range("T1"), Order1:=xlAscending, Header:=xlGuess, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin
                
'Need to add a filter of only rows that are < today minus one

        rgTarg.Formula = rgTarg.Value
        Worksheets("Dashboard").Range("D9").Value = rgTarg.Value 'Send value to Dashboard
'        .AutoFilter     'Turn filters off
    End With
End With

Sheets("MDL - Summary").Select
Sheets("Format").Select
'    Range("C4").Select
    
End Sub

Open in new window

0
Comment
Question by:RWayneH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36543660
Have you tried with the same syntax as line 16, ie:

.AutoFilter Field:=22, Criteria1:=Array(2, Format(Date - 1, "m/d/yyyy")) 'Today minus one

Do you want today and yesterday?

.AutoFilter Field:=22, Criteria1:=Array(2, Format(Date - 1, "m/d/yyyy")), Operator:=xlOr, Criteria2:=Array(2, Format(Date - 1, "m/d/yyyy"))

Thanks
Rob H
0
 

Author Comment

by:RWayneH
ID: 36544000
Need everything <= to yesterday.  Thanks. -R-
0
 

Author Comment

by:RWayneH
ID: 36544220
I just tried those two lines on Field:=20 and both lines return today minus one.  I need it to say <= to today, which will give me everything older than today. -R-
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Accepted Solution

by:
RWayneH earned 0 total points
ID: 36544938
Figured it out.

.AutoFilter Field:=20, Criteria1:="<" & Date, Operator:=xlAnd

Thanks for the help!!  -R-
0
 

Author Closing Comment

by:RWayneH
ID: 36565476
Thanks for the help....  -R-
0
 

Author Comment

by:RWayneH
ID: 36544953
Figured it out on my own. -R-
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question