VBA FILTER function: SourceArray argument causing Type Mismatch error?

[magemaester]

Hi guys,

I have a piece of code which requires using the FILTER Function, however the SourceArray argument is giving me a Type mismatch error, any help would be greatly appreciated.

This doesn't work

Dim bDistinct as Boolean
Dim arrList2() Variant

Redim arrList2(1 to 1559, 1)
arrList2 = Range("A2:A1560").Values

bDistinct = UBound(Filter(SourceArray:=arrList2, Match:="banana", Include:=True, Compare:=vbBinaryCompare)) < 0

This works

Dim bDistinct as Boolean
Dim arrList2() Variant

Redim arrList2(1)

bDistinct = UBound(Filter(SourceArray:=arrList2, Match:="banana", Include:=True, Compare:=vbBinaryCompare)) < 0

So just from these two examples I guessed that the error is either because

1) ReDim arrList2(1 to 1559, 1) makes this essentially a 2D array which isn't permitted by the SourceArray argument, or
2) Using the arrList2 = Range("A2:A1560").Values turns whatever string I had in the original cells into Numbers, which is also not permitted by the SourceArray

Regarding 1), if I change the ReDim statement to ReDim arrList2(1 to 1559), it still doesn't work. So this just leaves possibility 2), which I don't know how to fix.

If anyone can offer a solution that would be really appreciated. Thanks!!

 

[JoeMo]

The Filter function is looking for a 1-D array. Try it this way:

Dim arrList2 As Variant, bDistinct As Boolean
arrList2 = Application.Transpose(Range("A2:A1560"))
bDistinct = UBound(Filter(SourceArray:=arrList2, Match:="banana", Include:=True, Compare:=vbBinaryCompare)) < 0

 

[magemaester]

Hi JoeMo,

Thanks for the answer. This worked.

Just to provide you with some feedback/clarity on the exact logic of what went wrong :

After tinkering with it for a while, I realised that the FILTER function can ONLY accept an array of horizontal elements as its SourceArray. Therefore, my original code wasn't working even though I had the "ReDim (1 to 1559, 1)" because the following line arrList2 = Range("A2:A1560").Values overrides my ReDim and turns arrList2 into a vertical array of 1559 elements according to the direction of the original data on my spreadsheet. Hence why your transpose worked.

 

[JoeMo]

Hi JoeMo,

Thanks for the answer. This worked.

Just to provide you with some feedback/clarity on the exact logic of what went wrong :

After tinkering with it for a while, I realised that the FILTER function can ONLY accept an array of horizontal elements as its SourceArray. Therefore, my original code wasn't working even though I had the "ReDim (1 to 1559, 1)" because the following line arrList2 = Range("A2:A1560").Values overrides my ReDim and turns arrList2 into a vertical array of 1559 elements according to the direction of the original data on my spreadsheet. Hence why your transpose worked.

You are welcome.

Your original code was problematic from the start because, as I said in Post #2 , the Filter function is designed for a 1-D array and your arrList2 is a 2-D array. Using the Transpose turns the vertical range into a 1-D (horizontal) array.