count cells that don't contain a formula

[Jonathan Brown]

I'm trying to use something like CountA, or CountIf, or even Countifs but I
can't figure out what criteria I should use.

I just want to count the cells in a range that don't contain a formula. I'm
sure this is an easy one. It's gotta be.

Thanks to anyone who can help me figure this out.

 

[Mike H]

Hi,

A UDF maybe. Alt+F11 to open VB editor. Right click 'This Workbook' and
insert module and paste the code below in

Call with

=noformula(a1:a10)

Function noformula(rng As Range) As Long
For Each c In rng
If Not c.HasFormula Then
noformula = noformula + 1
End If
Next
End Function

Mike

 

[Bernard Liengme]

You will need to used VBA. Here is a simple User Defined Function that does
what you want

Function noform(myrange)
For Each mycell In myrange
If mycell.HasFormula = False Then
noform = noform + 1
End If
Next mycell
End Function

You may wish to visit David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes

 

[Jonathan Brown]

This is great, I've created my own UDF named =Jonathan() (pretty original
eh?) However, I'm running into a problem. What I'm doing with my macro is
finding the needed range and then placing the formula =Jonathan(myrange) into
the activecell. And then I'm using the autofill method (using xlDefault
autofill type) to copy the formula across about 51 columns. When I use the
autofill method, all the cells except the activecell return #Value.

If I do the autofill manually with my mouse then the formulas work. Is the
fact that I'm using the autofill method with a UDF messing it up? Is there
something else I need to include in the UDF to specify how to use the
autofill method?

 

[Jonathan Brown]

Isn't there like a recalculate method to force the cells to refresh, or
something?

 

[Jonathan Brown]

This is the absolute last thing I gotta get working and then my job is done.
I'm desperate.

 

[Bernard Liengme]

Send me your file (take TRUENORTH out of my address)
best wishes