How to take a range input in a function and find the frequency of data in it.

[Sourav]

Hi,
I am trying to write a function which takes a range and returns the
frequency of a data in it.

the function is :-

function freq ( q as variant , datarange as range)
count = 0
for i = 1 to datarange.rows.count
if datarange.cells(i,1). value = q then count= count+1
next i
freq = count
end function

this is not working, can any one suggest what is the prob. I just
learning VBA for excel .
is there any site where i can find tutorial for these, (I have
experience of C.) and a ebook to learn VBA for excel.

Thanks
Sourav

 

[Bernard Liengme]

Define 'is not working'
I entered some simple integers into A1:A20
To find how may 2's were in the range I use =freq(2,A1:A20) and it gave 8;
this agrees with a visual count and with =COUNTIF(A1:A20,2)

I then replaced some of my numbers with the word CAT
=freq("cat",A1:A20) and =COUNTIF(A1:A20,"cat") gave the same correct result.

Tell why you think your function is not working
best wishes

 

[Ken Johnson]

Hi Sourav,

It worked for me.

However, why aren't you just using Excel's COUNTIF worksheet function?
The only difference is the arguments are in reverse order, Range of
data first, then value being counted.

Ken Johnson

 

[Sourav]

thank you
its working in this form I was actualy tring to see whether the range
was in rows or colums and then performing the loop to get the
frequency.
Function freq(lb As Variant, ub As Variant, datarange As range)
Count = 0
If datarange.Columns.Count = 1 Then
For c = 1 To datarange.Rows.Count
d = datarange.Cells(c, 0).Value
d = y.Value
If d <= ub And d > lb Then Count = Count + 1
Next
Else
If datarange.Rows.Count = 1 Then
For c = 1 To datarange.Columns.Count
d = datarange.Cells(0, c).Value
If (d <= ub & d > ib) Then Count = Count + 1
Next
End If
End If
freq = Count
End Function

this was not working.
i was basicalu tring to find the freq. in a range.

and if i wish to write countif( ) function my self how can i take the
operators, i.e "<", ">" etc in my function.

Regards
Sourav