Code is not correct

[Pat]

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

 

[Macgru]

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

try to
MsgBox "There is data in column I28:I950, find and delete this data." & _
vbNewLine & "You cannot have any data in the Quantity Ordered
column when you are" & _
vbNewLine & "creating a Quick Sale invoice."

mcg

 

[Guest]

What do you want it to do?

 

[Pat]

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.

 

[Guest]

As the help for COUNTA indicates, it will count any *non empty* cell. XL
defines a non-empty cell as one that contains anything in it including a
formula that makes it *appear* that the cell is empty (as in your case).

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

Just for the record COUNTIF(E1:F10,"=") will count cells that are truly
empty. Don't ask me why.

 

[Dave Peterson]

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

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

 

[Pat]

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

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]

How about this one that uses =countblank() to look for cells that look empty:

Dim myRng As Range
Set myRng = Range("I28:I950")

If Application.CountBlank(myRng) = myRng.Cells.Count Then
MsgBox "they all LOOK empty"
Else
MsgBox "something besides """" in those cells"
End If

 

[Dave Peterson]

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

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

 

[Dave Peterson]

ps. You're right about that formula. I only checked with text--not numbers.