Countif Vba Arrays

mosu002 · May 15th 2007

Hello. I am trying to use the countif function in vba on memory only arrays. For example:

Code

Sub mem_countif()


    Dim a(), b, c
    
    a = Array(1, 2, 3, 4, 5)
    b = Application.Count(a)
    c = Application.CountIf(a, ">3")


End Sub

Display More

should give b = 5 and c = 2. Currently b = 5 but c = an array with 5 x 2015 errors.

Any ideas?

shg · May 15th 2007

Re: Countif Vba Arrays

I think you need a conventional loop to do what you're trying to do with CountIf.

filo65 · May 15th 2007

Re: Countif Vba Arrays

try

Code

Sub mem_countif()
  Dim a(), i&, iCnt&
  a = Array(1, 2, 3, 4, 5)
  iCnt = 0
  For i = 0 To UBound(a, 1)
    iCnt = iCnt - (a(i) > 3)
  Next i
  MsgBox iCnt
End Sub

filippo

mosu002 · May 15th 2007

Re: Countif Vba Arrays

Thank you, though can already do loops. Was trying to keep to worksheet functions, as I believe that they are quicker that UDFs.

Cheers anyway.

shg · May 15th 2007

Re: Countif Vba Arrays

Good plan, in general -- they are faster, when they can be used.

Countif Vba Arrays

Participate now!

Share