代码之家 › 专栏 › 技术社区 › polonius11

带数据转换的Excel VBA自定义项计数Ifs

user-defined-functions excel vba

polonius11 · 技术社区 · 8 年前

我正在尝试使用Countifs编写UDF。我希望用户能够选择标准1(保留为X1)和月份(保留为month_X)。查找表中的月份是数字,因此我希望函数将月份名称转换为月份编号。

这是可行的,但是有更好的方法来转换数据吗?一、 e.更少的代码行?

Function CountIF_Custom(X1 As String, Month_X As String)
    Dim MonthArray(1 To 12, 1 To 2) As Variant

    MonthArray(1, 1) = "January"
    MonthArray(2, 1) = "February"
    MonthArray(3, 1) = "March"
    MonthArray(4, 1) = "Apr"
    MonthArray(5, 1) = "May"
    MonthArray(6, 1) = "June"
    MonthArray(7, 1) = "July"
    MonthArray(8, 1) = "August"
    MonthArray(9, 1) = "September"
    MonthArray(10, 1) = "October"
    MonthArray(11, 1) = "November"
    MonthArray(12, 1) = "December"

    MonthArray(1, 2) = "1"
    MonthArray(2, 2) = "2"
    MonthArray(3, 2) = "3"
    MonthArray(4, 2) = "4"
    MonthArray(5, 2) = "5"
    MonthArray(6, 2) = "6"
    MonthArray(7, 2) = "7"
    MonthArray(8, 2) = "8"
    MonthArray(9, 2) = "9"
    MonthArray(10, 2) = "10"
    MonthArray(11, 2) = "11"
    MonthArray(12, 2) = "12"

    For x = 1 To 12
        If Month_X = MonthArray(x, 1) Then
            Month_X = MonthArray(x, 2)
        End If
    Next x

    CountIF_Custom = Application.CountIfs(Sheets("SomeSheet").Range("SomeRange"), X1, Sheets("SomeSheet").Range("SomeRange"), Month_X)

    End Function

1 回复 | 直到 8 年前

Absinthe 8 年前

不需要阵列,您可以将for循环与MonthName函数一起使用:

Function CountIF_Custom(X1 As String, Month_X As String)

Dim x As Integer

For x = 1 To 12
    If Month_X = MonthName(x) Then
        Month_X = CStr(x)
        Exit For
    End If
Next x

CountIF_Custom = Application.CountIfs(Sheets("SomeSheet").Range("SomeRange"), X1, Sheets("SomeSheet").Range("SomeRange"), Month_X)

End Function

推荐文章

Kram Kramer · 通过vba自动调整文本大小以适应形状

1 年前