[SOLVED] Another issue to resolve

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" <twogilvy@msn.com> wrote in message
news:%23nM6eK2FFHA.3732@tk2msftngp13.phx.gbl...
> 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
> >
> >
>
>

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" <glass_patrick@hotmail.com> wrote in message
news:%23aCzpk2FFHA.4004@tk2msftngp13.phx.gbl...
> 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" <twogilvy@msn.com> wrote in message
> news:%23nM6eK2FFHA.3732@tk2msftngp13.phx.gbl...
> > 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
> > >
> > >
> >
> >
>
>

> 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" <twogilvy@msn.com> wrote in message
news:OgDbyR3FFHA.3596@TK2MSFTNGP12.phx.gbl...
> 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" <glass_patrick@hotmail.com> wrote in message
> news:%23aCzpk2FFHA.4004@tk2msftngp13.phx.gbl...
> > 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" <twogilvy@msn.com> wrote in message
> > news:%23nM6eK2FFHA.3732@tk2msftngp13.phx.gbl...
> > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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" <glass_patrick@hotmail.com> wrote in message
news:eB7yfT4FFHA.2180@TK2MSFTNGP10.phx.gbl...
> > 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" <twogilvy@msn.com> wrote in message
> news:OgDbyR3FFHA.3596@TK2MSFTNGP12.phx.gbl...
> > 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" <glass_patrick@hotmail.com> wrote in message
> > news:%23aCzpk2FFHA.4004@tk2msftngp13.phx.gbl...
> > > 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" <twogilvy@msn.com> wrote in message
> > > news:%23nM6eK2FFHA.3732@tk2msftngp13.phx.gbl...
> > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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" <twogilvy@msn.com> wrote in message
news:eF0eZb4FFHA.3472@TK2MSFTNGP09.phx.gbl...
> 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" <glass_patrick@hotmail.com> wrote in message
> news:eB7yfT4FFHA.2180@TK2MSFTNGP10.phx.gbl...
> > > 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" <twogilvy@msn.com> wrote in message
> > news:OgDbyR3FFHA.3596@TK2MSFTNGP12.phx.gbl...
> > > 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" <glass_patrick@hotmail.com> wrote in message
> > > news:%23aCzpk2FFHA.4004@tk2msftngp13.phx.gbl...
> > > > 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" <twogilvy@msn.com> wrote in message
> > > > news:%23nM6eK2FFHA.3732@tk2msftngp13.phx.gbl...
> > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>