Use An Excel Built-In Function Entirely Within VBA

[Guest]

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?

 

[Ron Rosenfeld]

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

 

[Guest]

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.

 

[Ron Rosenfeld]

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