Using multiple criteria on Autofilter

Question

I have supplier data containing information about all the contracts (ongoing and completed). I need to filter out the ongoing contract of a supplier selected using a combobox.

When I use multiple criteria in the autofilter function in VBA, there are no rows returned. The code doesn't give any error.

Data:

col1,col2,col3,col4,col5;
[1]supplier name,project name,start of contract(date),end of contract(date),total amount
[2]aaa,xx,01/01/2014,31/10/2016,1000000
[3]bbb,yy,01/01/2015,31/10/2016,1000000
[4]ccc,zz,01/01/2013,31/10/2015,1000000
[6]ccc,aa,01/01/2016,31/10/2016,1000000
[7]bbb,bb,01/01/2014,31/10/2014,1000000
[8]aaa,cc,01/01/2015,31/10/2016,1000000

Code:

Private Sub Workbook_Open()
sName = InputBox("supplier name?")

'ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("$A$1").AutoFilter Field:=1, Criteria1:=sName
ActiveSheet.Range("$A$1").AutoFilter Field:=4, Criteria1:= _
    ">" & Date, Operator:=xlAnd
End Sub

The code works when I remove the date criterion, but doesn't return anything when using both the criteria.

For supplier 'aaa' the code should return 2 rows
for supplier 'bbb' : 1 row and
for supplier 'ccc' : 1 row

Rory Rory · Accepted Answer · 2016-01-22T11:29:55

Dates can be problematic in code if you don't use US regional settings. Try converting the date to a Double:

ActiveSheet.Range("$A$1").AutoFilter Field:=4, Criteria1:=">" & CDbl(Date)

Using multiple criteria on Autofilter

1 Answers