• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 144
  • Last Modified:

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

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
DAVID131
Asked:
DAVID131
  • 2
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
And what is wrong? Are we meant to guess?
0
 
DAVID131Author Commented:
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
 
Ejgil HedegaardCommented:
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
 
DAVID131Author Commented:
Thank you for your quick response
Your solution works fine
David
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now