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

VBA Why does Application.Countif return an array or error 424

提问by Kerry

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

我想计算数组中匹配项的数量。我尝试使用 Application.Countif

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

but this returns an array full of errors rather than a simple count. I have also tried using Application.WorksheetFunction.Countifbut this causes a 424 error.

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

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).

我目前正在使用单元格中的简短名称列表在工作表上进行测试A1:A20，但最终我计划将此代码与非常大的 CSV 文件一起使用，并且我想在使用 CountIf 之前将信息加载到数组中（而不是使用范围） .

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?

谁能建议我做错了什么？

回答by Instant Breakfast

You have several problems.

你有几个问题。

Using CountIf

使用 CountIf

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

首先，如果我理解正确，您是有意尝试Application.WorksheetFunction.CountIf在数组上使用该语句。那只会引起麻烦，因为CountIf（正如声明所暗示的那样）是“工作表函数”而不是“VBA 数组函数”。

Ditto has created a solution that uses CountIfcorrectly, by setting a range in the worksheeton which the CountIfstatement performs its job. If all you want is a way to count the value within that range, that is the way to go.

DittoCountIf通过在语句执行其工作的工作表中设置一个范围，创建了一个正确使用的解决方案CountIf。如果您想要的只是一种计算该范围内的值的方法，那就是要走的路。

Creating an array from a range

从范围创建数组

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.

其次，如果您真的需要将项目从工作表中取出并放入数组中（例如，如果您打算以不想影响工作表的方式使用这些值），您应该知道您只有部分解决了从范围选择中创建值数组的问题。

1. You are correct that to establish an array by assigning a range to a variable you need a variant, but you have forgotten the parenthesis, which are an essential part of denoting an array.
   
   So, instead of Dim Myrange As Variantyou should use Dim Myrange () As Variant

2. Having established MyRangeas 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 .Valuefor this. VBA will automatically do that for you.
   
   So, in your case you want to use the CurrentRegionfor 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 ActiveSheetbecause 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.

1. 您是正确的，通过为变量分配范围来建立数组，您需要一个变体，但是您忘记了括号，这是表示数组的重要组成部分。
   
   所以，而不是Dim Myrange As Variant你应该使用Dim Myrange () As Variant

2. 建立MyRange为数组后，您现在可以通过说明MyRange = Range("x")x 是被捕获区域的位置来分配数组值。您不需要（或不想）.Value为此使用。VBA 会自动为你做这件事。
   
   所以，你的情况，你想用CurrentRegion的Range("A1")是这样做哪些：MyRange = Range("A1").CurrentRegion。您还可以使用一个严格定义的范围，如下所示：MyRange = Range("A1:A12")或MyRange = Range("C7:F14")。
   
   注意：我离开了，ActiveSheet因为它在为数组分配范围时不起作用。假设您正在使用活动工作表，并且当前区域用于Range("x")语句中指示的单元格。

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.

第三，一旦您成功创建了数组，您将无法使用Countif（如上所述）。您将需要创建一种计算数组中该值的方法。这样做有几个考虑因素。

1. 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:

   Dim Row As Long Dim Col As Long

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

   Dim RowNumber As Integer RowNumber = UBound(MyRange, 1) Dim ColNumber As Integer ColNumber = UBound(MyRange, 2)

1. 由于从一个范围创建的数组将是二维的并且可能有不止一列，因此您不应假设只有一列。您将需要创建一个保存行数和列数的变量，以便您可以遍历整个数组。像这样的东西：

   Dim Row As Long Dim Col As Long

2. 您将需要使用UBound数组维度的来定义循环的限制。像这样的东西：

   Dim RowNumber As Integer RowNumber = UBound(MyRange, 1) Dim ColNumber As Integer ColNumber = UBound(MyRange, 2)

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

回答by Davesexcel

Just because this horse hasn't been beat enough already..here is a 1 liner

只是因为这匹马还没有被打败……这是一个 1 班轮

Sub Button3_Click()
    MsgBox Application.WorksheetFunction.CountIf(Range("A1:a20"), "Addison")
End Sub

回答by Ditto

Try this:

尝试这个：

  Sub TestCount()
      Dim MyCount
      Dim Myrange As Range
      Dim val As String
      val = "Addison"
      Set Myrange = ActiveSheet.Range("A1:a20")
      MyCount = Application.WorksheetFunction.CountIf(Myrange, val)
      MsgBox (MyCount)
  End Sub

1) define "Myrange" as a RANGE, not a variant.

1) 将“Myrange”定义为 RANGE，而不是变体。

2) use "set" keyword to assign range to Myrange

2）使用“set”关键字将范围分配给Myrange

3) give it the range you want: "a1:a20", not just "a1"

3）给它你想要的范围：“a1：a20”，而不仅仅是“a1”

回答by joshua Yoas

Yes, you didn't declare you range as a range type, so you didn't set the range.

是的，您没有将范围声明为范围类型，因此您没有设置范围。

Sub Macro1()
Dim val as String
Dim r As Range
Set r = Range("a1:a20")
val = "Addison"
MsgBox Application.WorksheetFunction.CountIf(r, val)
End Sub

or

或者

Sub CritSrh_Column()
Dim cell As Variant
Dim counter As Integer
For Each cell In Range("A1:A20")
'could use ("A:A") to search the whole column #not recommended#
'for dynamic rows, use end.xl('direction')
    If cell.Value = "Addison" Then
        counter = counter + 1
    End If
Next
MsgBox counter
End Sub