Use An Excel Built-In Function Entirely Within VBA

I'm not sure if this is possible or not, but I thought I'd give it a whirl.

If I have variables called SOME_ADDRSS, START_CELL and END_CELL (all strings
which contain valid cell addresses), I could use code to do something like
this:

Range(SOME_ADDRESS).Formula = "=COUNTIF(" & START_CELL & ":" & END_CELL &
","">0"")"
intResult = Range(SOME_ADDRESS).Value
Range(SOME_ADDRESS).Clear

intResult has the value I'm looking for. However, I need to write to a
certain range in the sheet in order to get it. I'd prefer to not do that, for
several obvious reasons. I don't want to have to check whether SOME_ADDRESS
is being used for actual value, etc. However, COUNTIF accomplishes exactly
what I want, and call me lazy but I don't want to reinvent the wheel if I
don't have to.

Is there a way I can get use of the COUNTIF functionality strictly within
VBA, and not just write my own version of it?

--
Hmm...they have the Internet on COMPUTERS now!

On Thu, 23 Feb 2006 09:11:28 -0800, MDW <[email protected]> wrote:

>I'm not sure if this is possible or not, but I thought I'd give it a whirl.
>
>If I have variables called SOME_ADDRSS, START_CELL and END_CELL (all strings
>which contain valid cell addresses), I could use code to do something like
>this:
>
>Range(SOME_ADDRESS).Formula = "=COUNTIF(" & START_CELL & ":" & END_CELL &
>","">0"")"
>intResult = Range(SOME_ADDRESS).Value
>Range(SOME_ADDRESS).Clear
>
>intResult has the value I'm looking for. However, I need to write to a
>certain range in the sheet in order to get it. I'd prefer to not do that, for
>several obvious reasons. I don't want to have to check whether SOME_ADDRESS
>is being used for actual value, etc. However, COUNTIF accomplishes exactly
>what I want, and call me lazy but I don't want to reinvent the wheel if I
>don't have to.
>
>Is there a way I can get use of the COUNTIF functionality strictly within
>VBA, and not just write my own version of it?

intResult = Application.WorksheetFunction.CountIf _
(Range(START_CELL, END_CELL), ">0")


--ron

Wow....I don't think, in the history of my use on these boards, I've ever had
a reply to so EXACTLY gave me what I wanted.

You're my new hero.

--
Hmm...they have the Internet on COMPUTERS now!


"Ron Rosenfeld" wrote:

> On Thu, 23 Feb 2006 09:11:28 -0800, MDW <[email protected]> wrote:
>
> >I'm not sure if this is possible or not, but I thought I'd give it a whirl.
> >
> >If I have variables called SOME_ADDRSS, START_CELL and END_CELL (all strings
> >which contain valid cell addresses), I could use code to do something like
> >this:
> >
> >Range(SOME_ADDRESS).Formula = "=COUNTIF(" & START_CELL & ":" & END_CELL &
> >","">0"")"
> >intResult = Range(SOME_ADDRESS).Value
> >Range(SOME_ADDRESS).Clear
> >
> >intResult has the value I'm looking for. However, I need to write to a
> >certain range in the sheet in order to get it. I'd prefer to not do that, for
> >several obvious reasons. I don't want to have to check whether SOME_ADDRESS
> >is being used for actual value, etc. However, COUNTIF accomplishes exactly
> >what I want, and call me lazy but I don't want to reinvent the wheel if I
> >don't have to.
> >
> >Is there a way I can get use of the COUNTIF functionality strictly within
> >VBA, and not just write my own version of it?
>
> intResult = Application.WorksheetFunction.CountIf _
> (Range(START_CELL, END_CELL), ">0")
>
>
> --ron
>

On Thu, 23 Feb 2006 11:41:17 -0800, MDW <[email protected]> wrote:

>Wow....I don't think, in the history of my use on these boards, I've ever had
>a reply to so EXACTLY gave me what I wanted.

That's related in no small part to your explaining EXACTLY what you wanted!

>
>You're my new hero.

<blush>


--ron