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