Wrong data type in WorksheetFunction.Filter

Question

I'm trying to make a function MonstersInLevel() that filters the second column of my "LevelMonsters" named range based on the value of the first column. The range's first column represents a game level ID and the second column represents a monster ID that appears in that level. Here's what my range looks like.

If I call MonstersInLevel(2) I expect the function to return a range consisting of "2", "3" and "4".

Function MonstersInLevel(level As Integer) As Range
    MonstersInLevel = Application.WorksheetFunction.Filter(Range("LevelMonsters").Columns(2), Range("LevelMonsters").Columns(1) = level)
End Function

I get:

A value used in the formula is of the wrong data type

I'm using the FILTER function as I would as an Excel formula. I assume there's some difference in the Excel and VBA syntax for FILTER's criteria.

Answer 1

Just encountered this problem myself and wanted to post my workaround.

We need to return an array of True/False to the worksheet function. To do this I created a Function that takes a 2D array, the column wanted and the value to compare. It then returns a 2d single column array of the necessary True/False.

Function myeval(arr() As Variant, clm As Long, vl As Variant) As Variant()
    Dim temp() As Variant
    ReDim temp(1 To UBound(arr, 1), 1 To 1)
    
    Dim i As Long
    For i = 1 To UBound(arr, 1)
        temp(i, 1) = arr(i, clm) = vl
    Next i
    
    myeval = temp
End Function

So in this particular case it would be called:

Function MonstersInLevel(level As Integer) As Variant
    MonstersInLevel = Application.WorksheetFunction.Filter(Range("LevelMonsters").Columns(2), myeval(Range("LevelMonsters").Value, 1, level),"""")
End Function

Answer 2

Avoid type mismatch in Worksheetfunction via VBA

Keeping in mind that the 2nd argument reflects a dynamic matrix condition based entirely on ►worksheet logic (returning an array of 0 or 1 cell values /False or True]) it seems that you have

• to execute an evaluation at least within this argument and
• declare the function type (explicitly or implicitly) as Variant

Function MonstersInLevel(level As Integer) As Variant
    ''   Failing assignment:
    '    MonstersInLevel = Application.WorksheetFunction.Filter(Range("LevelMonsters").Columns(2), _
    '        Range("LevelMonsters").Columns(1) = level _
    '        )
    MonstersInLevel = Application.WorksheetFunction.Filter( _
                      Range("LevelMonsters").Columns(2), _
                      Evaluate(Range("LevelMonsters").Columns(1).Address & "=" & level) _
                      )
End Function

...or to evaluate the complete function

Function MonstersInLevel(level As Integer) As Variant
    Dim expr As String
    expr = "=Filter(" & _
           Range("LevelMonsters").Columns(2).Address & "," & _
           Range("LevelMonsters").Columns(1).Address & "=" & level & _
           ")"
    'Debug.Print expr
    MonstersInLevel = Evaluate(expr)
End Function

Example call writing results to any target

Dim v
v = MonstersInLevel(2)
Sheet1.Range("D2").Resize(UBound(v), UBound(v, 2)) = v

Of course it would be possible as well to write .Formula2 expressions programmatically, even splitting into spill range references.

---

Addendum ........... //as of Jan 10th

Backwards compatible workaround via VBA.Filter()

"If you guys know any other VBA function that would be more appropriate than Application.WorksheetFunction.Filter I'd be ok."

In order to provide also a backwards compatible alternative, I demonstrate the following approach using the classic (VBA.)Filter() function (see section [3]) based upon prior matching results (see [1]).

Note that Application.Match() comparing two (!) array inputs delivers a whole array of possible findings (instead of a single result as most frequently executed). Non findings are identified by IsError() values of -1; adding +1 results in a set of zeros and ones. Section [2] enters corresponding data for positive findings. Eventually non-findings (i.e. 0or zero) are removed by a tricky negative filtering.

Function getLevels()

Function getLevels(rng As Range, ByVal level As Long)
'Site: https://stackoverflow.com/questions/65630126/how-to-remove-only-the-duplicate-row-instead-of-removing-all-the-rows-that-follo
    '[0] get datafield array
    Dim v, v2
    v = Application.Index(rng.Value2, 0, 1)     ' 1st column
    v2 = Application.Index(rng.Value2, 0, 2)        ' 2nd column
    '[1] check data (with Match comparing 2 arrays :-)
    Dim results
    results = Application.Transpose(Application.Match(v, Array(level), 0))
    '[2] rebuild with False/True entries
    Dim i As Long
    For i = 1 To UBound(results)
        results(i) = IsError(results(i)) + 1        ' 0 or 1-values
        If results(i) Then results(i) = v2(i, 1)    ' get current value if true
    Next i
    '[3] remove zeros (negative filtering)
    results = Filter(results, "0", False)
    '[4] return results as vertical 1-based array
    getLevels = Application.Transpose(results)
End Function

Example call

    Const LVL = 2       ' define level
    With Sheet1                                     ' change to project's sheet Code(Name)
        'define data range (assuming columns A:B)
        Dim rng As Range
        Set rng = .UsedRange.Resize(, 2)
        'function call getLevels()
        Dim levels
        levels = getLevels(rng, level:=LVL)
        'write to target
        .Columns("I:I").Clear
        .Range("I2").Resize(UBound(levels), 1) = levels
    End With

Answer 3

Solution without any supporting VBA function:

Function MonstersInLevel(level As Integer) As Variant
    With Application.WorksheetFunction
        MonstersInLevel = .Filter(Range("LevelMonsters").Columns(2), _
        .IfError(.XLookup(Range("LevelMonsters").Columns(1), level, True), False))
    End With
End Function

XLookup returns an array of #N/A or True. IfError replaces errors with False. Finally, the Filter function receives an array of booleans as the second parameter.

EDIT

Removed the IfError function thanks to @ScottCraner:

Function MonstersInLevel(level As Integer) As Variant
    With Application.WorksheetFunction
        MonstersInLevel = .Filter(Range("LevelMonsters").Columns(2), _
        .XLookup(Range("LevelMonsters").Columns(1), level, True, False))
    End With
End Function

Answer 4

I couldn't resolve your question but as I did some testing on the subject trying to do so, I thought I'd share my findings:

Based on this Microsoft community post, or at least the answers there, it seems you will need to loop through the output in one way or another...

That question seems to want to achieve the same as what you are wanting to do (I think?).

On the other hand, I have never used the WorksheetFunction.Filter method, and the closest I could get it to working was like so:

Here is my sample data - RangeOne is Column A and RangeTwo is Column B. I have used the =FILTER() function in cell C1 evaluating the input in D1 for reference of expected results. Naturally this function is working as expected! The VBA routine is outputting to Columns E, F and G.

Sub TestFilterFunction()
    Dim TestArray As Variant
    
    Range("E1:E3") = Application.Filter(Range("RangeTwo"), Range("RangeOne"), Range("D1"))
    Range("F1:F3") = Application.Filter(Range("RangeTwo"), Range("RangeOne") = Range("D1")) 'Runtime Error 13
    Range("G1:G3") = Application.Filter(Range("RangeTwo"), Range("RangeOne"))
    
    TestArray = Application.Filter(Range("RangeTwo"), Range("RangeOne"), Range("D1"))
    TestArray = Application.Filter(Range("RangeTwo"), Range("RangeOne") = Range("D1"))      'Runtime Error 13
    TestArray = Application.Filter(Range("RangeTwo"), Range("RangeOne"))

    Range("H1:H3") = Application.Filter(Range("RangeTwo", "RangeOne"), Range("RangeOne"), Range("D1"))
    TestArray = Application.Filter(Range("A1:B9"), Range("RangeOne"), "2")

End Sub

Column E returned the first 3 values from RangeTwo. Column F has not been populated - This is because that line threw the Runtime error 13 - Type Mismatch Column G returned the first 3 values from RangeTwo. Column H returned the first 3 values from "A1:B9" (both ranges together) - specifically the first 3 values of column A.

I thought this was odd so I threw in an array to assign the values to rather than directly to the worksheet;

The first TestArray line and the third TestArray line both populated the array with the entire RangeTwo values;

I realised with the syntax of the first and third attempt at the WorksheetFunction.Filter, the entire range is returned (that being the first argument - Arg1 - range), but when trying to include the = Range("D1") , it returns the Type Mismatch error.

The final TestArray attempt being the same syntax as the Column H test, returned both columns in a 2D array (now TestArray(1 To 9, 1 To 2)).

I should note I couldn't find any documentation at all on WorksheetFunction.Filter so I'm assuming it does follow the same syntax as the Excel Sheet Function has.

If I find anything more on this topic I'll come back and edit it in, but for now it's looking like perhaps a solution using either loops or Index/Match functions also will need to happen to have the data returned in VBA.

I thought about perhaps writing the sheet formula to a cell and then grab that into an array or something but Excel inserts @ into it now which only returns a single cell result, i.e.

Range("J1").Formula = "=FILTER(B1:B9, A1:A9 = D1)"

Would return in J1:

=@FILTER(B1:B9, A1:A9 = D1)

Which with our sample data, would only return 2 in J1 as opposed to the expected/desired 2, 3 and 4 in J1:J3.

I can't work out a way to remove the @ as it is applied when the function is written to the cell unfortunately, but hopefully any of the above helps someone find a solution.

Answer 5

Just some comments to help you out.

If you are using the new FILTER() function from either a worksheet cell or within some VBA code, the first argument should be a range and the second argument should a a Boolean array. (if you don't enter something that can evaluate to a Boolean array, VBA may complain the the data type is wrong)

You would be best served (in VBA) if you:

1. explicitly declared a 2 dimensional, column-compatible, Boolean array
2. filled the array
3. used the array in the function call

Here is a super simple example. Say we want to filter the data from A1 to A6 to remove blanks. We could pick a cell and enter:

=FILTER(A1:A6,A1:A6<>"")

Looks like:

Now we want to perform the same activities with a VBA sub and put the result in a block starting with B9. The code:

Sub SingleColumn()
    Dim r As Range, wf As WorksheetFunction, i As Long
    Dim arr, s As String, dq As String, boo, rc As Long
    
    Set wf = Application.WorksheetFunction
    Set r = Range("A1:A6")
    rc = r.Rows.Count
    ReDim boo(1 To r.Rows.Count, 1 To 1) As Boolean
    
    i = 1
    For Each rr In r
        If rr.Value = "" Then
            boo(i, 1) = False
        Else
            boo(i, 1) = True
        End If
        i = i + 1
    Next rr

    arr = wf.Filter(r, boo)
    
    MsgBox LBound(arr, 1) & "-" & UBound(arr, 1) & vbCrLf & LBound(arr, 2) & "-" & UBound(arr, 2)
    
    Range("B9").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Result: