VBA: Countif array

[pplstuff]

Excel 2007.

Say I have an array, MyArray = array(2, 4, 6, 10, 4). How can I do a countif() of that array?

Specifically, how could I do:
for i = 1 to 5
j = countif(MyArray, MyArray(i))
.cells(i, 1) = j
next i

So the result would be
1
2
1
1
2


Any thoughts? Thanks in advance!!

 

[JoeMo]

Excel 2007.

Say I have an array, MyArray = array(2, 4, 6, 10, 4). How can I do a countif() of that array?

Specifically, how could I do:
for i = 1 to 5
j = countif(MyArray, MyArray(i))
.cells(i, 1) = j
next i

So the result would be
1
2
1
1
2


Any thoughts? Thanks in advance!!

The countif worksheet function is looking for a range object, so if you want to use it you would need to do something like this (uses B1:F1 to hold the array values - this range can be changed to any range of comparable size you want to use). Remember that MyArray as you have defined it is a zero-based array.

Sub CountArray()
Dim MyArray As Variant, R As Range
MyArray = Array(2, 4, 6, 10, 4)
Set R = Range(Cells(1, 2), Cells(1, UBound(MyArray) + 2))
R.Value = MyArray
For i = 0 To 4
    j = WorksheetFunction.CountIf(R, MyArray(i))
    Cells(i + 1, 1) = j
Next i
End Sub

 

[pplstuff]

Thanks, JoeMo (as always).

I was hoping to do it without using cells. The end goal is finding 5-card draw payoffs for a poker game (when the hands look like "2h, Qd, Ac, 5h, 5d" as an example).

--I could do it easily doing worksheet functions and pasting cells around. I was hoping to improve my ability to work with arrays in VBA. Just a personal exercise.

 

[shg]

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.

 

[hiker95]

pplstuff,

Maybe something like this, with the results written to a range for better understanding?


Sample worksheet:

Excel Workbook
	A	B
1
2
3
4
5
6
7
Sheet1

After the macro:

Excel Workbook
	A	B
1	MyArray	Count
2	2	1
3	4	2
4	6	1
5	10	1
6	4	2
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Option Base 1
Sub CountArrayItems()
' hiker95, 12/05/2012
' http://www.mrexcel.com/forum/excel-questions/672885-visual-basic-applications-countif-array.html
Dim MyArray, r, i As Long, ii As Long, n As Long
MyArray = Array(2, 4, 6, 10, 4)
ReDim r(1 To UBound(MyArray))
For i = 1 To UBound(r)
  n = MyArray(i)
  For ii = LBound(MyArray) To UBound(MyArray)
    If MyArray(ii) = n Then
      r(i) = r(i) + 1
    End If
  Next ii
Next i
Cells(1, 1).Resize(, 2).Value = [{"MyArray","Count"}]
Cells(2, 1).Resize(UBound(MyArray)).Value = Application.Transpose(MyArray)
Cells(2, 2).Resize(UBound(r)).Value = Application.Transpose(r)
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the CountArrayItems macro.

 

[hiker95]

pplstuff,

Private Mesage from pplstuff:

Clever approach! That is exactly what I was looking to do. Thank you.

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.