☰

Excel VBA 工作表函数“过滤器”出现“类型不匹配”错误

tag-icon • tag-icon microsoft-excel worksheet-function vba
Excel VBA 工作表函数“过滤器”出现“类型不匹配”错误

下面是我尝试从值列表中过滤“0”。分配“结果”时，它给出错误“运行时错误‘13’：类型不匹配”。

Function FilterVals(list_of_vals As Range)

Dim result As Variant
result = Application.WorksheetFunction.Filter(list_of_vals, list_of_vals <> 0)
FilterVals = result

End Function

我认为错误可能出在这个语法“list_of_vals <> 0”中。

“A1:A5<>0” 在 Excel 工作表中运行良好，但在 Excel VBA 中可能不那么简单……

答案1

在使用时Worksheetfunction.Filter，第二个参数必须是数组{True, False,..}，正确定位（即垂直或水平，取决于要过滤的内容) 作为你的论据。

在您的代码中，产生该结果的一种方法可能是：

result = Application.WorksheetFunction.Filter(list_of_vals, Evaluate(list_of_vals.Address & "<> 0"))

假设list_of_vals是一维数组

答案2

我需要根据单元格值进行过滤。我尝试了 Ron Rosenfeld 的方法，但需要进行调整。这有效：

Dim result() As Variant

With WorksheetFunction
    result = .Filter(.Transpose(Evaluate(list_of_vals.Address)),_
    .Transpose(list_of_vals.Address & "=" & criteria_cell.Address)))
End With

答案3

我对此进行了更多尝试，现在我认为我已经理解得相当透彻了。下面是一个测试子程序，用于展示我的探索结果。对于数据，请将以下内容放在 A1 的开头。

返回 筛选
苹果 水果
橙子 水果
牛扒 肉
鸡 肉
饼干 甜点
蛋糕 甜点
萝卜 蔬菜
西兰花 蔬菜
卷心菜 蔬菜
西瓜 水果
香蕉 水果
绿豆 蔬菜
冰淇淋 甜点 
Sub WSF_Filter_Test()
 'Note:  As Ron pointed out, the 2nd .Filter argument must be an array of boolean values.  All of the 'Evaluate' calculations below result in boolean values.
    With WorksheetFunction
        'works
         x1 = Evaluate(Range("B2:B14").Address & "=" & Range("B2").Address) '2d array 
         result1 = .Filter(.Transpose(Evaluate(Range("A2:A14").Address)), .Transpose(x1)) '1d array with 4 elements as expected
         
         x1b = Evaluate(Range("B2:B14").Address & "=" & Range("B2").Address) '2d array
         result1b = .Filter(.Transpose(Evaluate(Range("A2:B14").Address)), .Transpose(x1)) '2d array with 4 elements in each dimension as expected

         x2 = Evaluate(Range("B2").Address & "=" & Range("B2").Address) 'single value
         result2 = .Filter(.Transpose(Evaluate(Range("A2").Address)), Array(x2)) '1d array with a single element
         
         x3 = Evaluate(Range("B2").Address) = "Fruit"
         result3 = .Filter(.Transpose(Evaluate(Range("A2").Address)), Array(x3)) '1d array with a single element
        
        'Type Mismatch at runtime
        'x4 = Evaluate(Range("B2:B14").Address) = "Fruit"
        
        'This runs, but the resulting 2d array has only 'Error 2029' (#NAME) errors
        x4 = Evaluate(Range("B2:B14").Address & "= Fruit")
        
        
        'Works
        'Build the boolean array by looping through each cell in the Filter column
         Dim x5()
         Dim rngReturn5 As Range
         Dim rngFilter5 As Range
         
         Set rngFilter5 = Range("B2:B15")
         Set rngReturn5 = Range("A2:A15")
         
         ReDim x5(1 To rngFilter5.Rows.Count, 1 To 1)
         For i = 1 To UBound(x5)
            x5(i, 1) = rngFilter5.Cells(i) = "Fruit"
         Next i
         result5 = .Transpose(.Filter(Evaluate(rngReturn5.Address), x5)) '1d array with 4 elements as expected
      
      
         Dim x5b()
         Dim rngReturn5b As Range
         Dim rngFilter5b As Range
         
         Set rngFilter5b = Range("B2:B15")
         Set rngReturn5b = Range("A2:B15")
         
         ReDim x5b(1 To rngFilter5b.Rows.Count, 1 To 1)
         For i = 1 To UBound(x5b)
            x5b(i, 1) = rngFilter5b.Cells(i) = "Fruit"
         Next i
         result5b = .Transpose(.Filter(Evaluate(rngReturn5b.Address), x5b)) '2d array with 4 elements in each dimension as expected
         
         
        'But if you're looping through the range and want a single column returned, might as well just build the array and avoid the Filter worksheetfunction
         Dim result6()
         Dim rngReturn6 As Range
         Dim rngFilter6 As Range
         Dim k as Integer, l As Integer
         
         Set rngFilter6 = Range("B2:B15")
         Set rngReturn6 = Range("A2:A15")
    
         For k = 1 To rngFilter6.Rows.Count
            If rngFilter6.Cells(k) = "Fruit" Then
                l = l + 1
                ReDim Preserve result6(1 To l)
                result6(l) = rngReturn6.Cells(k)
            End If
         Next k
    End With
End Sub

相关内容