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.

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

"Jonathan Brown" wrote:

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.

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jonathan Brown" wrote in message
...
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.

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?

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jonathan Brown" wrote in message
...
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.

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

"Jonathan Brown" wrote:

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?

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jonathan Brown" wrote in message
...
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.

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

"Jonathan Brown" wrote:

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

"Jonathan Brown" wrote:

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?

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jonathan Brown" wrote in message
...
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.

Send me your file (take TRUENORTH out of my address)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jonathan Brown" wrote in message
...
This is the absolute last thing I gotta get working and then my job is
done.
I'm desperate.

"Jonathan Brown" wrote:

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

"Jonathan Brown" wrote:

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?

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jonathan Brown" wrote in
message
...
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.