count cells that don't contain a formula
 

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.

count cells that don't contain a formula
 

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.

count cells that don't contain a formula
 

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.

count cells that don't contain a formula
 

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.

count cells that don't contain a formula
 

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.

count cells that don't contain a formula
 

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.

count cells that don't contain a formula
 

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.