Filter Array Type mismatch

[itchyII]

Hi all,
I am trying to populate an array with a named range in Excel and then search the array for a value. All I have read on it has suggested that I should be able to use the 'Filter' funtion to accomplish this, but I keep getting a Type Mismatch error. As I understand it, the search criteria in the function must be a string, but my array is a variant (this is what works best for popuating with a range of cells). But I am seeing many examples out there that search a string into a variant array, so why am I getting the error? I have been trying to get this for too long! I figure it should be pretty simple. What am I missing?

Dim a As Integer
Dim y As Integer
Dim key As String
Dim keyArray() As Variant
Dim found As Variant

'count rows
x = countRows(Range("A1"))

'set named range
Range("B2:B" & x).Select
ActiveWorkbook.Names.Add Name:="_key", RefersToR1C1:= _
"=GroupConversionReport!R2C2:R" & x & "C2"
ActiveWorkbook.Names("_key").Comment = ""

'populate array
keyArray() = Range("_key")

'select other sheet
Sheets("ProjectViewGroups_B").Select

'count rows
y = countRows(Range("A1"))
a = 2

Range("N1").Select
ActiveCell.FormulaR1C1 = "Rule Found"

While a <> y
Range("N" & a).Select
If ActiveCell.Offset(0, -11).Value = "Project" Then
key = ActiveCell.Offset(0, -10).Value & ActiveCell.Offset(0, -7) & ActiveCell.Offset(0, -4)
found = Filter(keyArray, key)
a = y
Else
a = a + 1
End If
Wend

 

[PHV]

why am I getting the error
I guess that keyArray isn't a 1-dimension array but a 2-dimension.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[itchyII]

Hi, thanks for the response.

Thats it. I just explicitly set the dimensions of the array before loading it and now I get the error on the loading of the array with my range of data. So, the question now is, how come it sees my named range as multi-dimensional? It's only one column! I tried removing the use of the named range and explicitly passing the range directly, but it still gives me the error.

 

[PHV]

A Range is a two-dimensional array (row,column), dot.

 

[itchyII]

Ok, so I guess I have no choice to iterate through the column to populate my array.....arghh.....

 

[SkipVought]

You might want to abandon the array approch and simply use the Find and FindNext methods in a Do Loop. That way, you need not interrogate each value in the range.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[itchyII]

Hmmmm....
What would be more efficient?. The array would hold aproximately 10000 entries and I will be searching the array approximately 10000 times. Would it be more efficient to populate the array once through iteration and then use the 'Filter' function for the 10000 search items, or loop through the 10000 search items and find them in the range using the 'Find' method. (note that the search could return multiple 'finds' which would each need further processing). Ideas?

 

[SkipVought]

It seemed that your filter approch was not working. Processing an array is almost always the better apporch. Iwas throwing the Find method out there in the event that your original plan ran into significant problems.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[itchyII]

Thanks. I will try it with the array. I think it will be faster (although seemingly tedious!)