Countif for VBA array without a loop

[sheetspread]

These two posts suggest it can't be done:

http://www.mrexcel.com/forum/excel-questions/672885-visual-basic-applications-countif-array.html

http://www.mrexcel.com/forum/excel-...lications]-count-number-occurances-array.html


But shg said:

You can use worksheet functions in VBA, and could use WorksheetFunction.SumProduct for this. Some functions (CountIf, Rank, some others) work only with ranges (and IntelliSense will tell you), but SumProduct is happy with arrays.

I tried using evaluate sumproduct with a myarray (as variant) and couldn't get it to work.

Example: myarray = array("First","Second","Second","Third","Fourth")

then what would the countif(myarray,"second") syntax be? (the result should equal 2 counts)


The only other method I can think of is printing the array to a worksheet range (which doesn't require a loop), counting off that, then deleting.

 

[patel45]

Application.WorksheetFunction.CountIf works with ranges, not array

Sub a()
Set Rng = Range("A1:A5")
n = Application.WorksheetFunction.CountIf(Rng, "Second")
MsgBox n
End Sub

 

[Rick Rothstein]

These two posts suggest it can't be done:

http://www.mrexcel.com/forum/excel-questions/672885-visual-basic-applications-countif-array.html

http://www.mrexcel.com/forum/excel-...lications]-count-number-occurances-array.html


But shg said:

I tried using evaluate sumproduct with a myarray (as variant) and couldn't get it to work.

Example: myarray = array("First","Second","Second","Third","Fourth")

then what would the countif(myarray,"second") syntax be? (the result should equal 2 counts)

The only other method I can think of is printing the array to a worksheet range (which doesn't require a loop), counting off that, then deleting.

How about doing it with a home-made function...

Function ArrayCountIf(ArrayIn As Variant, ByVal ElementValue As Variant, _
                      Optional CaseSensitive As Boolean) As Long
  Dim D As String, SearchString As String
  D = Chr$(0)
  SearchString = Chr(1) & Join(ArrayIn, Chr(1) & Chr(1)) & Chr(1)
  If Not CaseSensitive Then
    ElementValue = UCase(ElementValue)
    SearchString = UCase(SearchString)
  End If
  ArrayCountIf = UBound(Split(SearchString, Chr(1) & ElementValue & Chr(1)))
End Function

The function takes two required arguments and a third optional one. The first argument is the one-dimensional VB array, the second item is what you want to count and the third is an optional Boolean indicating whether the search should be case sensitive or not (the default is False, meaning searches are not case sensitive). Note... this function does not permit the use of wildcards.

 

[Ron Coderre]

Try this:

Sub test()
Dim myarray
Dim mycount As Integer
Dim iCtr As Integer
myarray = Array("First", "Second", "Second", "Third", "Fourth")
For iCtr = 0 To UBound(myarray)
       mycount = mycount + Abs(myarray(iCtr) = "Second")
Next iCtr
MsgBox mycount
End Sub

Does that work for you?

 

[pgc01]

Hi

Try also:

MsgBox Application.Count(Application.Match(myArray, Array("Second"), 0))

 

[sheetspread]

That's it! Thanks PGC, Rick, and everyone else

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

 

[sheetspread]

I also liked your formula to find the last blank cell in a range. Both save lots of code lines.

 

[pgc01]

Thank you. It's funny to try to find a 1-liner for these type of questions.

We must, however, not forget that sometimes they may not be the most efficient solution. Sometimes, loop solution with test conditions, that need some lines of code and variable declarations and initialisations, may seem duller but be more efficient.

On the other hand in most of the solutions you'll not be worried if the code take 1/10th of a second more to execute.

 

[nchittim]

Hi patel,

Thanks for the above code, please suggest me above code to write without msgbox ,It should be result in another column,

Help would be appreciated,

Thanks,
Nagaraju