Solved

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Viewers will learn how to apply various conditional formatting in Excel 2013.
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …

746 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

12 Experts available now in Live!

Get 1:1 Help Now