Count number of specific values in an array (VBA)

[selant]

I want to count how many "woman" values in my person array. Example as follows :
Can anybody share the solution for the function if there is ? Thank you.

Private Sub CommandButton1_Click()


Dim person(1 To 5) As String


person(1) = "man"
person(2) = "woman"
person(3) = "woman"
person(4) = "man"
person(5) = "man"


Label1.Caption = "There are 2 women" 'calculate how many "woman" in person array
Label2.Caption = "There are 3 men" 'calculate how many "man" in person array


End Sub

 

[Special-K99]

Something like

Sub k1()

Dim person(1 To 5) As String


person(1) = "man"
person(2) = "woman"
person(3) = "woman"
person(4) = "man"
person(5) = "man"

w1 = 0
m1 = 0
For i = 1 To 5
If person(i) = "woman" Then w1 = w1 + 1
If person(i) = "man" Then m1 = m1 + 1
Next i
Debug.Print "There are "; w1; " women"
Debug.Print "There are "; m1; " men"

End Sub

 

[VoG]

Maybe

Label1.Caption = "There are " & Application.CountIf(person, "woman") 'calculate how many "woman" in person array

 

[selant]

Thank you VoG for the solution.

 

[Firefly2012]

Did you try the Countif suggestion?

 

[selant]

i am getting

Compile Error :
Type Mismatch error

for

Label1.Caption = "There are " & Application.CountIf(person, "woman") 'calculate how many "woman" in person array

</pre>where is the error ?

 

[Firefly2012]

The error is that Countif doesn't work with arrays (only ranges). Looping is the way to go (especially with a limited dataset as your example had) as Special-K99 originally showed you,

 

[selant]

Thank you Firefly 2012 and Special-K99 for the working solution.

 

[Scott Huish]

Private Sub CommandButton1_Click()
Dim person(1 To 5) As String, mw(0 To 1) As Long, i As Long
person(1) = "man"
person(2) = "woman"
person(3) = "woman"
person(4) = "man"
person(5) = "man"
For i = 1 To 5
    mw(0) = mw(0) - (person(i) = "woman")
    mw(1) = mw(1) - (person(i) = "man")
Next
Label1.Caption = "There are " & mw(0) & " women"
Label2.Caption = "There are " & mw(1) & " men"
End Sub

 

[selant]

Thank you HOTPEPPER,

yes, that worked.