Counting entries in a range
 

Hi,

I have an array, defined by:

vNetScore = .Range(.Range("s3"), .Range("s100")).Value

Valid values within this array can be anything between 0 and 25.

I need to count the number of values within sets of sub-ranges in this array:
Number of occurrences of 0-6
Number of occurrences of 7 - 14
Number of occurrences of 15 - 21
Number of occurrences of 22-25

Hope this makes sense!

This is in Office '07 under Vista.

Can anyone provide the code to do this?

TIA

Dave

Counting entries in a range
 

Hi,

Here's a sledghammer way

Sub versive()
Set vNetScore = Range("s3:s100")
For Each c In vNetScore

Select Case c.Value
Case Is = 22
twentytwo = twentytwo + 1
Case Is = 14
fifteen = fifteen + 1
Case Is = 7
seven = seven + 1
Case Is = 0
zero = zero + 1
End Select


Next
Debug.Print twentytwo
Debug.Print fifteen
Debug.Print seven
Debug.Print zero
End Sub


Mike

"Risky Dave" wrote:

Hi,

I have an array, defined by:

vNetScore = .Range(.Range("s3"), .Range("s100")).Value

Valid values within this array can be anything between 0 and 25.

I need to count the number of values within sets of sub-ranges in this array:
Number of occurrences of 0-6
Number of occurrences of 7 - 14
Number of occurrences of 15 - 21
Number of occurrences of 22-25

Hope this makes sense!

This is in Office '07 under Vista.

Can anyone provide the code to do this?

TIA

Dave

Counting entries in a range
 

Hello Dave,

Array-enter into 4 adjacent vertical cells:
=Freq(S3:S100)

Freq is this UDF:
Function freq(r As Range) As Variant
freq = Application.WorksheetFunction.Frequency(r, Array(6, 14, 21,
25))
End Function

Regards,
Bernd