获取格式为(A1:A15)的单元格的范围

Question

这可能是一个简单的问题，但我还找不到答案我有一个单元格，其中包含单元格范围作为A1:A15格式的文本。我正试图编写一个函数来接收单元格的实际范围(A1:A15)，但它只给我实际单元格的地址(B4)。

我怎么能得到范围本身，而不是单元格的地址?

功能是:

Function IsSick(ByVal Arr As Range) As Integer
   Dim i, k As Integer
   k = 0
   Arr.Select
   For i = 2 To Arr.Count
       If Arr(i) >= 37 Then k = k + 1
   Next i
   IsSick = k
End Function

thank you in advance

Answer 1

试试这个:

Function IsSick(ArrAdr As Range) As Integer
    Dim i As Integer, k As Integer
    Dim arr As Range
    Set arr = Range(ArrAdr.Value)
    k = 0
    For i = 2 To arr.Count
        If arr(i) >= 37 Then k = k + 1
    Next i
    IsSick = k
End Function

或更好:

Function IsSick(ArrAdr As Range) As Integer
    IsSick = Application.CountIf(Range(ArrAdr.Value), ">=37")
End Function

还请注意，当您使用Dim i, k As Integer时，只有k具有Integer类型，而i是Variant。因此，您应该使用Dim i As Integer, k As Integer。

和阅读:如何避免使用Select/Active语句