Xl2007 Autofilter - ValueList problem

[OssieMac]

This post looks long but there is really only one line of the code giving
problems and the remainder is purely to provide the full example so I hope
someone can help me with this and point out what I am missing here.

I can record code while setting a list of numeric values in AutoFilter and I
can then return the ValueList with code as per the following example (works
fine):-

Sub ValueListFilterNumbers()
Dim arrCriteria1()
Dim i As Long
'Recorded code to filter on valuelist of numbers
'Criteria1 is an array
ActiveSheet.Range("$A$1:$D$185") _
.AutoFilter Field:=1, _
Criteria1:= _
Array("1", "3", "5", "7"), _
Operator:=xlFilterValues

'Assign Criteria1 to an array
With ActiveSheet.AutoFilter.Filters(1)
arrCriteria1() = .Criteria1
End With

'Read the values in the array
For i = 1 To UBound(arrCriteria1)
MsgBox arrCriteria1(i)
Next i

End Sub

If I record code as per the following example while setting a list of dates
then it appears that the array is assigned to Criteria2. I thought that I
should be able to assign Criteria2 to an array similar to Criteria1 above but
it does not work and I get a runtime error 1004. (See code between asterisk
lines)

Sub ValueListFilterDates()
Dim arrCriteria2()
Dim i As Long

'Recorded code to filter on valuelist of dates
'Criteria2 is an array
ActiveSheet.Range("$A$1:$D$185") _
.AutoFilter Field:=2, _
Operator:=xlFilterValues, _
Criteria2:=Array(2, "10/1/2009", _
2, "10/3/2009", _
2, "10/5/2009", _
2, "10/7/2009", _
2, "10/9/2009")

'Assign Criteria2 to an array
With ActiveSheet.AutoFilter.Filters(2)
'******************************************
'This code returns runtime error 1004
arrCriteria2() = .Criteria2
'******************************************
End With

'I want to do the following but without the previous
‘code it obviously will not work.
‘Read the values in the array
For i = 1 To UBound(arrCriteria2)
MsgBox arrCriteria2(i)
Next i

End Sub

The following test shows that the array on its own from the above code can
be assigned to an array variable so it is not a problem mixing numeric and
dates. The problem seems to be with assigning .Criteria2 to an array.

Sub arrayTest()

Dim test()
Dim i As Long
test() = Array(2, "10/1/2009", _
2, "10/3/2009", _
2, "10/5/2009", _
2, "10/7/2009", _
2, "10/9/2009")

For i = 0 To UBound(test)
MsgBox test(i)
Next i

End Sub

 

[Patrick Molloy]

your filter is four columns but your date array has five dates.

 

[OssieMac]

Hi Patrick,

No! That's not the problem. The filters are all set on column 2 (Filter 2)
as a list of values. The Operator is xlFilterValues (can not do this in xl
before version xl2007).

The code to set the filters is recorded code and works. Attempting to return
the values is the problem.

 

[Patrick Molloy]

oops. I see

Hi Patrick,

No! That's not the problem. The filters are all set on column 2 (Filter 2)
as a list of values. The Operator is xlFilterValues (can not do this in xl
before version xl2007).

The code to set the filters is recorded code and works. Attempting to return
the values is the problem.

 

[Patrick Molloy]

unfortunately I don't have access to my home PC, so I can't run with 2007.
I'll check later if this is still unresolved.

best regards

 

[Joel]

Autofilter will produce an error if no values were found in the worksheet and
doesn't work with a range. At least I wasn't able to get it to work. the
only way I got it to work was to put a namedrange in the workbook and ut
advance filter.


With ActiveSheet
Set FilterRange = .Range("$A$1:$D$185")

'get 2nd column of autofilter range
Set Col2 = FilterRange.Offset(0, 1).Resize(, 1)
Col2.AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("arrCriteria2"), _
Unique:=False
End With

 

[OssieMac]

Hi Joel,

I am not using Advanced filter or filter in place. I am using AutoFilter in
xl2007. Setting multiple values like this could not be done prior to xl2007.
The first part of the code to set the Autofilter works. I have only included
it to show the code that is recorded when the filter is set. In practice the
user will set it manually.

What I am trying to do is read the values of the filter after the user has
set it. This works perfectly with my first example using just numbers.
However, the second example is using dates and if when recording the code to
set the filter an array is assigned to Criteria2 then it should be possible
to assign Criteria2 to an array variable the same way as my first example
which works fine.

The reason that I want to read the criteria is because I am using AutoFilter
to create a dynamic chart. I want the criteria to use in the chart title. I
do this by having the user set the AutoFilter and then with worksheet
activate event when they select the sheet with the chart, the title is
automatically updated to reflect the criteria selected.

 

[Joel]

I don't have acces to 2007 so I can't duplicate the problem. I keep only
fijnding unusal probelms with dates. Excel sometimes switches them from
long, to integers, single, or strings. I can never predicate. Sometimes I
need to use DateValue to convert other times I have to use
format(MyDate,"M/D/YYYY"). the only way I'm usually able to solve these
probelms is stepping through the code and adding watch items until I'm able
to isolate the problem.

In 2003 autofilter gives the 1004 error if nothing is found by the filter.
I'm asuming this is the case. Therefore, the problem must be the dates are
in the wrong format.

Because the criteria2 has a mixture of dates and numbers in must be treated
as a variant. Therefore, variants dates are getting converted to serial
dates internal in excel. Autofilter wants the criteria dates to be strings
otherwise it doesn't get a match. If you made all the number into string in
criteria 2 then it may work

from
Array(2, "10/1/2009", _
2, "10/3/2009", _
2, "10/5/2009", _
2, "10/7/2009", _
2, "10/9/2009")

to
Array("2", "10/1/2009", _
"2", "10/3/2009", _
"2", "10/5/2009", _
"2", "10/7/2009", _
"2", "10/9/2009")

Maybe using an interdiate variable would help
Myarray1 = Array(2, "10/1/2009", _
2, "10/3/2009", _
2, "10/5/2009", _
2, "10/7/2009", _
2, "10/9/2009")

 

[OssieMac]

Joel,

Without xl2007 you can't do what I am trying to do. xl2007 allows the
selection of multiple values from the dropdown list and in doing so sets the
operator to xlFilterValues.
The following part of the code was created by recording the macro and works
fine and sets the filter when run.
ActiveSheet.Range("$A$1:$D$185") _
.AutoFilter Field:=2, _
Operator:=xlFilterValues, _
Criteria2:=Array(2, "10/1/2009", _
2, "10/3/2009", _
2, "10/5/2009", _
2, "10/7/2009", _
2, "10/9/2009")

However, in production the filter will not be set with code; it will be set
by the user and I want to be able to read the values set by the user. If
ordinary numbers are in the column being filtered then xl assigns them to
criteria1 as an array. If dates are in the column then xl assigns them to
criteria2 as an array. I want to know why I can't read criteria2 the same way
I read criteria 1. I thought I should be able to do it as follows but it will
not assign to an array like criteria1 does with numbers

With ActiveSheet.AutoFilter.Filters(2)
'Assign criteria2 to an array
arrCriteria2() = .Criteria2
End With

'Read the values in the array
For i = 1 To UBound(arrCriteria2)
MsgBox arrCriteria2(i)
Next i

Just for interest the 2 followed by a date in the array indicates individual
dates have been selected in the dropdown. If code contains a 1 followed by a
date, this means the entire month has been selected. In the following example
the months of Jul, Sep and Dec have been selected.
ActiveSheet.Range("$A$1:$D$185") _
.AutoFilter Field:=2, _
Operator:=xlFilterValues, _
Criteria2:=Array(1, "7/31/2009", _
1, "9/30/2009", _
1, "12/31/2009")