if cells(21,11).value = "QS" then
range("i28:i950").clearcontents
end if



Pat wrote:

The only other option open to me if I cannot get the correct code is to
incorporate code that would delete whatever is in the range I28:I950 if
"QS" is in cell:
If Cells(21, 11).Value = "QS" Then

Any ideas how this could be achieved?

Pat

"Pat" wrote in message
...
Hi folks,

Thank you for helping me out. I have been trying out our suggestions and
I
have been not getting the result I am looking for.

If Application.CountIf(Range("I28:I950"), """") 0 Then

The above did not return the message from the MsgBox as I have tried out
having a value greater than 0 in the range I28:I950. If there is a vale
in
any of the cells in the range I28:I950 the MsgBox should warn the user.


-----------------------------

If all the cells in I28:I950 contain the formula you mention, use
COUNTIF(E1:F10,"") This will count cells that are either truly empty or
appear empty because of a formula yields "".

I am not sure where E1:F10 comes from but I have modified the formula in
I28:I950 to look like:

=COUNTIF(J76,"=")

instead of:

=IF(J76="","",J76)

By using what I think is your suggestion this will yield 1 if J76 is empty
and empty if J76 contains a value.

Unfortunately this is not what is needed.

Regards
Pat




"Dave Peterson" wrote in message
...
=counta() includes all formulas--including those that evaluate to "".

Maybe you could use a different formula:

If Application.CountIf(Range("I28:I950"), """") 0 Then

(I like application.countif, but you could still use
worksheetfunction.countif.)

Pat wrote:

The messagebox appears even though there is no data in I28:I950
For some reason it thinks there is data in the range, only the formula
as
mentioned will be in the range.

"Tushar Mehta" wrote in
message
...
What do you want it to do?

"Pat" wrote:

The following code is not correct.


If WorksheetFunction.CountA(Range("I28:I950")) 0 Then
If Cells(21, 11).Value = "QS" Then
MsgBox "There is data in column I28:I950, find and
delete
this
data. You cannot have any data in the Quantity Ordered column when
you
are
creating a Quick Sale invoice. "
Exit Sub
End If
End If


There is a formula in cell in the range I28:I950
For eg. =IF(J76="","",J76)


I will appreciate any and all help.

Thanks
Pat




--

Dave Peterson



--

Dave Peterson