?
Solved

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

Posted on 2015-01-30
4
Medium Priority
?
124 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
[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
  • 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 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Viewers will learn how to apply various conditional formatting in Excel 2013.
Viewers will learn a basic relationship technique in Power Pivot for Excel 2013.

764 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