Solved

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

Posted on 2015-01-30
4
104 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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

This is an Add-On procedure to be used in conjunction with the code provided in Reducing EE Email Clutter using Outlook (http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/A_3146-Outlook-Processing-EE-emails-on-Receive.…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

832 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