VBA Why does Application.Countif return an array o

Question

I would like to count the number of matching items in an array. I tried using 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.Countif but this causes a 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).

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?

Answer 1

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

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

Answer 2

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

Answer 3

You have several problems.

Using CountIf

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

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.

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 Variant you should use Dim Myrange () As Variant

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

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.

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 UBound of the array dimensions. Something like this:

   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

Answer 4

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.

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

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