VBA为什么Application.Countif返回数组或错误424

问题描述

我想计数阵列匹配的项数。我试着用Application.Countif

I would like to count the number of matching items in an array. I tried using Application.Countif

MyCount = Application.WorksheetFunction.CountIf(Myrange, val)

但这返回一个数组充满了错误，而不是一个简单的计数。我一直在使用也尝试 Application.WorksheetFunction.Countif 但是这会导致 424错误。

我目前的工作表上测试其名称在细胞的简短列表 A1：A20 ，但最终我打算使用此code具有非常大的CSV文件，我想用COUNTIF（而不是使用范围）之前加载信息到一个数组。

I currently am testing on a worksheet with a short list of names in cells A1:A20, but ultimately I plan to use this code with a very large CSV file and I want to load the information into an array before using CountIf (rather than using the range).

Sub TestCount()
    Dim MyCount
    Dim Myrange As Variant
    Dim val As String
    val = "Addison"
    Myrange = ActiveSheet.Range("A1").CurrentRegion.Value
    MyCount = Application.WorksheetFunction.CountIf(Myrange, val)
    MsgBox (MyCount)
End Sub

任何人都可以建议我做错了什么？

Can anyone suggest what I did wrong?

推荐答案

您有几个问题。

首先，如果我的理解没错，你intentially试图使用 Application.WorksheetFunction.CountIf 语句的阵列上。这只会带来麻烦，因为 COUNTIF （为说明书建议）是一个工作表函数而不是VBA阵列功能。

First, if I understand right, you are intentially trying to use the Application.WorksheetFunction.CountIf statement on an array. That will only cause trouble, since CountIf (as the statment suggests) is a "worksheet function" not a "VBA Array function".

同上已经创建了一个使用的解决方案 COUNTIF 正确，通过在工作表的在其上 COUNTIF设置了一系列的 语句执行它的工作。如果你想要的是一个方法来计算该范围内的值，也就是要走的路。

Ditto has created a solution that uses CountIf correctly, by setting a range in the worksheet on which the CountIf statement performs its job. If all you want is a way to count the value within that range, that is the way to go.

第二，如果你真的需要得到的项目工作表后，进入阵列（例如，如果您打算使用的方式这些值来工作，你不想影响工作表），你应该知道您只部分解决从一系列的选择创造价值的一个数组的问题。

Second, if you really need to get the items out of the worksheet and into an array (for example, if you plan to work with those values in ways you don't want to effect the worksheet), you should know that you have only partially solved the question of creating an array of values from a range selection.

您是正确的，通过指定一个范围内，你需要一个变种变量建立一个数组，你却已经忘记了括号，这是表示一个数组中必不可少的一部分。搜索结果因此，而不是暗淡Myrange为Variant 你应该使用暗淡Myrange（）为Variant

在确定 MyRange 为一个数组，你现在可以说指定数组值 MyRange =范围（X）其中x是该地区被抓获。你并不需要（或希望）使用 .value的这一点。 VBA将自动为你做的。搜索结果所以，你的情况，你要使用的 CurrentRegion 为范围（A1）这是做这样的： MyRange =范围（A1）CurrentRegion 。你也可以使用一个严密规定的范围内是这样的： MyRange =范围（A1：A12）或 MyRange =范围（C7：F14 ）。搜索结果注意：我离开的 ActiveSheet 因为数组分配范围时，它不起作用。其假设是，您正在使用的活动表，以及当前区域是在范围（X）语句。

Having established MyRange as an array, you can now assign the array values by saying MyRange = Range("x") where x is the area being captured. You do not need to (or want to) use .Value for this. VBA will automatically do that for you. So, in your case you want to use the CurrentRegion for Range("A1") which is done like this: MyRange = Range("A1").CurrentRegion. You could also use a closely defined range like this: MyRange = Range("A1:A12") or MyRange = Range("C7:F14"). Note: I left off the ActiveSheet because it does not work when assigning ranges to arrays. The assumption is that you are using the active sheet, and the current region is for the cell indicated in the Range("x") statement.

第三，一旦你已经成功地创建一个数组，你将无法使用 COUNTIF （如上所述）。你需要创建阵列内计数的值的方法。有在做这几方面的考虑。

Counting values within the array

Third, once you have succeeded in creating an array, you won't be able to use Countif (as noted above). You'll need to create a method of counting that value within the array. There are several considerations in doing this.

由于从一系列创建的阵列将是二维的，并可能有多个列，你不能假设只有一个列。您将要创建包含行数和列数的个数的变量，这样就可以通过整个数组循环。事情是这样的：

Since an array created from a range will be two dimensional and may have more than one column, you should not assume just one column. You will want to create a variable that holds the number of rows and number of columns, so you can loop through the entire array. Something like this:

昏暗行只要 昏暗上校只要

您将要使用 UBound函数数组维度来定义循环的限制。事情是这样的：

You will want to define the limits of your loops using the UBound of the array dimensions. Something like this:

昏暗ROWNUMBER作为整数    ROWNUMBER = UBound函数（MyRange，1） 昏暗ColNumber作为整数    ColNumber = UBound函数（MyRange，2）

我觉得以下code会做你想要使用什么你尝试的方式创建一个数组：

Code for using an array to find your count

I think the following code will do what you want using an array created in the manner you were attempting:

Sub TestCount()

    Dim MyCount As Long
        MyCount = 0
    Dim MyRange() As Variant
        MyRange = Range("A1").CurrentRegion
    Dim val As String
        val = "Addison"
    Dim Row As Long
    Dim Col As Long
    Dim RowNumber As Long
        RowNumber = UBound(MyRange, 1)
    Dim ColNumber As Long
        ColNumber = UBound(MyRange, 2)

    For Col = 1 To ColNumber
        For Row = 1 To RowNumber
            If MyRange(Row, Col) = val Then MyCount = MyCount + 1
        Next Row
    Next Col

    msgbox MyCount

End Sub