[SOLVED] Another issue to resolve

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 <ec35720@netscapeXSPAM.com>
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" <anonymous@discussions.microsoft.com> wrote in message
news:5b4g11dbn0fs1h25rnsub6loi3qjas7lui@4ax.com...
> 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
<ec35720@netscapeXSPAM.com>
> 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 <ec35720@netscapeXSPAM.com>
> 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" <ec35720@netscapeXSPAM.com> wrote in message
news:421881C7.57DCBE89@netscapeXSPAM.com...
> 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
<ec35720@netscapeXSPAM.com>
> > 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" <glass_patrick@hotmail.com> wrote in message
news:eFmQfs0FFHA.2568@TK2MSFTNGP10.phx.gbl...
> 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" <ec35720@netscapeXSPAM.com> wrote in message
> news:421881C7.57DCBE89@netscapeXSPAM.com...
> > 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
> <ec35720@netscapeXSPAM.com>
> > > 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
>
>