Error using WorksheetFunction.Countif

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

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)

"Kris_Wright_77" <[email protected]> wrote in message
news:[email protected]...
> 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

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" wrote:

> 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)

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" wrote:

> 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)

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)

"Kris_Wright_77" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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)

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)

"Kris_Wright_77" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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)