Solved

Using VBA to filter a list using a criteria from an indirect cell

Posted on 2015-01-30
4
101 Views
Last Modified: 2016-02-10
A macro that has previously worked in Excel is not working when the filtered list is in a table.
The filter criteria is derived from a cell in another worksheet
Model-O-tables-access-extract-1.03.xlsm
0
Comment
Question by:DAVID131
  • 2
4 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40579972
And what is wrong? Are we meant to guess?
0
 

Author Comment

by:DAVID131
ID: 40580437
When you run the macro you will see that no values are brought through into the products sheet (there should be values for the date that is being used) as  it is not applying the date from 'Products' B1 to filter in 'Product Table' B1.
 I suspect it is due to how I have written;
ActiveSheet.ListObjects("Table_missouri_ComTrack_Test_Product").Range. _
        AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:="<=xfilter"
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 40581741
Criteria2:="<=xfilter" filters for the text "<=xfilter", not the date.
It also has to be Criteria1, so change to Criteria1:="<=" & xfilter

Perhaps xfilter converts to the serial date when used as a text in the criteria.
Not sure, since I have another date format than US, and the criteria has to be US date format.

I changed the lines
Dim xfilter As Date
xfilter = ThisWorkbook.Worksheets("Products").Range("B1").Value

to
Dim xfilter As String
xfilter = WorksheetFunction.Text(ThisWorkbook.Worksheets("Products").Range("B1").Value, "mm-dd-yyyy")
to be sure of the format.

See file.
Model-O-tables-access-extract-1.04.xlsm
0
 

Author Comment

by:DAVID131
ID: 40582344
Thank you for your quick response
Your solution works fine
David
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The code described here does no longer work. Please see replacement Article: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3887-Getting-your-EE-Ranking-statistics-in-Excel-The-Next-Generation.html (http…
0. Preface This Article is a replacement of http:/A_1788-Getting-your-EE-Ranking-statistics-in-Excel.html (http://http:/A_1788-Getting-your-EE-Ranking-statistics-in-Excel.html). Changes in the way Experts Exchange delivers point statistics, impleme…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Video by: Zack
Viewers will learn about various customizable options in Excel 2013.

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now