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

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

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

Hi folks,

I have followed your suggestion and I am not getting any satisfaction. Here
is different approaches I have tested:

v1
If Application.CountIf(Range("M28:M1000"), """""") 0 Then
If Cells(4, 4).Value = "QS" Then
MsgBox "There is data in column E1:E8, find and delete this
data "
Exit Sub
End If
End If

This will not inform the user if there is data in the range.


v2

If Application.CountIf(Range("M28:M1000"), """""") 0 Then
If Application.Count(Range("M28:M1000")) < 0 Then

If Cells(4, 4).Value = "QS" Then
MsgBox "There is data in column E1:E8, find and delete this
data "
Exit Sub
End If

End If
End If

This also will not inform the user if there is data in the range.


v3

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

If Cells(4, 4).Value = "QS" Then
MsgBox "There is data in column E1:E8, find and delete this
data "
Exit Sub
End If

End If

This will inform the user if there is data in the range and it will also
inform the user if there is no data in the range, which is not what I want.






"Myrna Larson" wrote in message
...
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

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

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

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

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

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

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

in E1 I would use a formula like

=if(A1="","",A1)

Then countblank will work with the cells in column E.

--
Regards,
Tom Ogilvy

"Pat" 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.
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

Tom, you are a genius -:)
That seems to have sorted that problem out.
Many thanks to you Dave and Myrna for all your help.

Cheers
Pat

"Tom Ogilvy" wrote in message
...
in E1 I would use a formula like

=if(A1="","",A1)

Then countblank will work with the cells in column E.

--
Regards,
Tom Ogilvy

"Pat" 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.
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