If E1 holds a formula
=A1
then if A1 is blank, E1 will display zero and countblank will not count it
as empty.
This comment is correct. What alternative is there available to me instead
of using countblank, where the zero in E1 is not counted?

There is nothing in the code you show that would write anything to any
cells
so I am not sure what you mean when you say:

If any cel in the range e1:e8 contain a result of the formula this cells
content is deleted

If there is an alternative option as mentioned above then when any of the
formulas in the range E1:E8 return a result then the user has permission to
manually delete say E4. Deletion of data which is the result of a formula
is only necessary if D4 contains "QS" If D4 contains any other data then it
will not be necessary for the message to appear. Any formulas deleted will
be reset using separate code. The code I posted earlier is only a portion
of a much larger piece of code, it will not be necessary to provide this to
the group as it is irrelevant.

Regards
Pat

"Tom Ogilvy" wrote in message
...
If E1 holds a formula
=A1
then if A1 is blank, E1 will display zero and countblank will not count it
as empty.

There is nothing in the code you show that would write anything to any
cells
so I am not sure what you mean when you say:

If any cel in the range e1:e8 contain a result of the formula this cells
content is deleted.

--
Regards,
Tom Ogilvy


"Pat" wrote in message
...
Actually on a small test sheet they are a simple formula which refers to
another cell on the same sheet:

E1 =A1 =number
through to:
E10 =A10 =number

D4 =QS

With Range("e1:e8")
If Cells(4, 4).Value = "QS" Then
If Application.CountBlank(.Cells) = .Cells.Count Then
MsgBox "all look blank"
Else
MsgBox "something looks like it's there"
End If
End With

If any cel in the range e1:e8 contain a result of the formula this cells
content is deleted. Even with doing this all the other formulas causes
the
message "something looks like it's there" to appear.

Pat


"Tom Ogilvy" wrote in message
...
The results are in the immediate window.

What do your formulas look like in column M. are they alike

=if(condition,"",number)

or are they like
=if(condition," ",number)

If like the second, make them like first and Dave's first posting code
should work.

--
Regards,
Tom Ogilvy



"Pat" wrote in message
...
Hi Dave,

I also am using xl2003 but when I ran your code it created a new
sheet
each
time with no feedback with the results you got.

Have you been able to figure out what I am trying to accomplish from
my
last
post?

Pat


"Dave Peterson" wrote in message
...
I tried it directly on the worksheet and it worked ok (xl2003).

I tried this code:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myRng As Range

Set wks = Worksheets.Add
With wks
Set myRng = .Range("a1:a10")
With myRng
Debug.Print "Empty: " & Application.CountBlank(.Cells)
myRng.Formula = "="""""
Debug.Print "formulas evaluating to """": " _
& Application.CountBlank(.Cells)
.Value = .Value
Debug.Print "after conversion to ' " _
& Application.CountBlank(.Cells)
End With
End With

End Sub

And got this:

Empty: 10
formulas evaluating to "": 10
after conversion to ' 10

And even though the help says that it counts empty cells, there's
a
remark
that
says:

Cells with formulas that return "" (empty text) are also counted.
Cells
with
zero values are not counted.


Myrna Larson wrote:

Hi, Dave:

In some brief testing that I just did, a cell containing a
formula
that
returns "" is not considered to be blank. The cell has to be
empty
to
be
included by COUNTBLANK.

If you have a column that contains a formula that returns either
text
or
"",
you can count the number of "non-blank looking" cells with

=COUNTIF(M28:M1000,"""")

If the formula returns either a number or "":

=COUNT(M28:M1000)

Translating those to code, I come up with

If Application.COUNTIF(Range("M28:M1000"),"""""") 0 Then

and

If Application.COUNT(Range("M28:M1000")) < 0 Then

On Sat, 19 Feb 2005 19:19:31 -0600, Dave Peterson

wrote:

How about looking at the number of blanks (cells that are empty
or
evaluate
to
""):

With Range("M28:m1000")
If Application.CountBlank(.Cells) = .Cells.Count Then
MsgBox "all look blank"
Else
MsgBox "something looks like it's there"
End If
End With

Pat wrote:

If WorksheetFunction.CountA(Range("M28:M1000")) 0 Then

Because each cell in the range contain a formula the line of
code
will
always return true regardless if no result has been returned
by
any
of the
formulas. Can anyone tell me if the code can be changed to
only
return true
if there is a result in any of the cells.

Thank U
Pat

--

Dave Peterson