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!

Use An Excel Built-In Function Entirely Within VBA
 

On Thu, 23 Feb 2006 09:11:28 -0800, MDW 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

Use An Excel Built-In Function Entirely Within VBA
 

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

Use An Excel Built-In Function Entirely Within VBA
 

On Thu, 23 Feb 2006 11:41:17 -0800, MDW 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