Another issue to resolve

[Pat]

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]

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

 

[Myrna Larson]

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

 

[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.

 

[Dave Peterson]

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.

 

[Pat]

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

 

[Tom Ogilvy]

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

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

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.

or

 

[Pat]

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

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

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


remark Cells
with

to

be

text

 

[Tom Ogilvy]

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:

 

[Pat]

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 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]

in E1 I would use a formula like

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

Then countblank will work with the cells in column E.

 

[Pat]

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