Error using WorksheetFunction.Countif

[Guest]

I have been writing some code to evaluate an array of numbers using some
worksheet functions, some of which work and some dont (1 so far)

I have loaded an 1-Dimensional array, (Five_Array), with 5 Numbers and then
used the following functions to evaluate

Var = WorksheetFunction.Var(Five_Array)
Ave = WorksheetFunction.Average(Five_Array)
Largest = WorksheetFunction.Large(Five_Array,1)
all of which work

However using CountIf causes 'Run-Time Error 424' Object Required
Occur = WorksheetFunction.Countif(Five_Array,Five_Array(1))
If Five Array contains (6,20,2,6,15) then Occur should = 2

Can anyone explain to me why it wont work for CountIf, as it will
undoubtedly occur when I add other Functions at a later date.

Thanks for any help that anyone can give.

Kris

 

[Bob Phillips]

That is because those 3 functions can work on an array of numbers, COUNTIF
cannot, it needs a range as the first argument. To demonstrate this, try
this in Excel

=AVERAGE({1,2,3,4,5})

Then try

=COUNTIF({1,2,3,4,5},3)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks for the explanation, Bob.

Is there a way around the problem without resorting to a "loop" or multiple
lines of similar code to see which numbers reoccur within the array?

Thanks for any more help that anyone can give.

Kris

 

[Bob Phillips]

You could dump the array to a worksheet range and then use that, but in
reality you just need to use the functions as designed. Just as you can't
pass a string to a function expecting a number and expect to get a
reasonable result, the same is true here.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)