Excel UDF过滤器范围 [英] Excel UDF Filter Range

问题描述

我有一个函数需要一个范围的值作为输入（只是一列）以及一些阈值。我想返回一个被过滤的范围，以包括原始范围内大于阈值的所有值。我有以下代码：

 公共功能FilterGreaterThan（Rng As Range，Limit As Double）As Range 
 
 Dim Cell As Range 
 Dim ResultRange As Range 
 
每个单元格在Rng 
如果Abs（Cell.Value）> = Limit Then 
如果ResultRange为没有了
设置ResultRange =单元格
 Else 
设置ResultRange = Union（ResultRange，Cell）
如果
结束If 
 Next 
 Set FilterGreaterThan = ResultRange 
结束函数
  

问题是一旦数字低于阈值，其他数字超过阈值之后，不会添加到范围内。

例如：

 阈值 -  2 
 
数字 -  
 
 3 
 4 
 1 
 5 
  

它将循环添加3和4，但不会添加5。我最终得到#value错误。但是我没有收到任何错误，如果我只输入范围-3,4或范围-3,4,1。

解决方案

ooo首先到达那里，但我现在已经输入了，所以我可以发布。该版本将作为正确大小的列向量返回。

如果没有匹配，那么＃1/1将返回一个1数组（这与

编辑2：更新功能感谢来自ooo的评论

 公共功能FilterGreaterThan（Rng As Range，Limit As Double）As Variant（）
 
 Dim inputCell As Range'我们从
中读取的每个单元格Dim resultCount As Integer发现的匹配单元格数
 Dim resultValue（）As Variant'单元格数组
 
 resultCount = 0 
 ReDim resultValue（1到1，1到Rng.Cells.Count）
 
对于每个inputCell在Rng 
如果Abs（inputCell.Value）> = Limit Then 
 resultCount = resultCount + 1 
 resultValue（1，resultCount）= inputCell.Value 
 End If 
 Next inputCell 
 
'输出数组必须是二维的，我们ca n only 
'ReDim保留最后一个维度
如果（resultCount> 0）然后
 ReDim保存resultValue（1到1，1到resultCount）
 Else 
 resultValue（1，1）= CVErr（xlErrNA）
 ReDim保存resultValue（1到1 ，1到1）
 End If 
 
'转置结果以生成列而不是行
 resultValue = Application.WorksheetFunction.Transpose（resultValue）
 
 FilterGreaterThan = resultValue 
 
结束功能
  

---

编辑：对于以下评论中的测试值，我可以使用：

我确定你知道这一点，但不包括 {或} 输入数组公式时的字符 - Excel在您按下Ctrl-Shift-Enter之后添加它们>

I have a function that takes a range of values as input (just a column) as well as some threshold. I would like to return a range that is filtered to include all values from the original range that are greater than the threshold. I have the following code:

Public Function FilterGreaterThan(Rng As Range, Limit As Double) As Range

Dim Cell As Range
Dim ResultRange As Range

For Each Cell In Rng
    If Abs(Cell.Value) >= Limit Then
        If ResultRange Is Nothing Then
            Set ResultRange = Cell
        Else
            Set ResultRange = Union(ResultRange, Cell)
        End If
    End If    
Next
Set FilterGreaterThan = ResultRange
End Function

The issue is that once a number is below the threshold, other numbers after that one that are above the threshold do not get added to the range.

For example:

Threshold - 2

Numbers -

3
4
1
5

It will loop through adding 3 and 4 but 5 will not be added. I end up getting a #value error. But I get no error and it works fine if I only enter the range - 3, 4 or the range - 3, 4, 1.

解决方案

ooo got there first but I've typed it out now so I may as well post it. This version will return as a column vector of the correct size.

If nothing matches then #N/A is returned in a 1 by 1 array (this is consistent with the normal behaviour of an array function when there are insufficient values to fill the array)

edit2: updated function thanks to comments from ooo

Public Function FilterGreaterThan(Rng As Range, Limit As Double) As Variant()

Dim inputCell As Range ' each cell we read from
Dim resultCount As Integer ' number of matching cells found
Dim resultValue() As Variant ' array of cell values

resultCount = 0
ReDim resultValue(1 To 1, 1 To Rng.Cells.Count)

For Each inputCell In Rng
    If Abs(inputCell.Value) >= Limit Then
        resultCount = resultCount + 1
        resultValue(1, resultCount) = inputCell.Value
    End If
Next inputCell

' Output array must be two-dimensional and we can only
' ReDim Preserve the last dimension
If (resultCount > 0) Then
    ReDim Preserve resultValue(1 To 1, 1 To resultCount)
Else
    resultValue(1, 1) = CVErr(xlErrNA)
    ReDim Preserve resultValue(1 To 1, 1 To 1)
End If

' Transpose the results to produce a column rather than a row
resultValue = Application.WorksheetFunction.Transpose(resultValue)

FilterGreaterThan = resultValue

End Function

---

edit: works OK for me with the test values in the comment below:

I'm sure you know this but don't include the { or } characters when entering the array formula - Excel adds them in after you've hit Ctrl-Shift-Enter

这篇关于Excel UDF过滤器范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！