When K22 contains "QS" and there is data in column M a message will appear.

If WorksheetFunction.CountA(Range("M28:M1000")) = _
WorksheetFunction.Count(Range("M28:M1000")) Then
If Cells(22, 11).Value = "QS" Then
MsgBox "There is data in column M28:M1000, find and delete this
data "
Exit Sub
End If

The problem I am encountering is that any data appearing in column M is the
result of a formula. How can I have the code ignore the formula and only
recognize the result of the formula.

Thanks
Pat

After having deleted the formulas in M28:M1000 the code still returns an
error. The problem seems to relate to K22. Whenever QS is replaced with a
different data the problem doses not appear.
Here is more of the code:


If Not IsEmpty(Cells(22, 11)) Then _
Cells(22, 11).Value = _
Application.Substitute(Cells(22, 11), "qs", "QS")


Selection.AutoFilter Field:=10, Criteria1:="<"

If IsEmpty(Cells(22, 11)) Then
ans = MsgBox("You must enter an Order No. If no Picking List has been
generated then enter QS instead. QS denotes Quick Sale.", vbOKOnly)
Range("K22").Select
Exit Sub
End If

If WorksheetFunction.CountA(Range("M28:M1000")) = _
WorksheetFunction.Count(Range("M28:M1000")) Then
If Cells(22, 11).Value = "QS" Then
MsgBox "There is data in column M28:M1000, find and delete this
data "
Exit Sub
End If
End If

Hope someone can pinpoint the problem
Thanks
Pat


"Pat" wrote in message
...
When K22 contains "QS" and there is data in column M a message will
appear.

If WorksheetFunction.CountA(Range("M28:M1000")) = _
WorksheetFunction.Count(Range("M28:M1000")) Then
If Cells(22, 11).Value = "QS" Then
MsgBox "There is data in column M28:M1000, find and delete
this
data "
Exit Sub
End If

The problem I am encountering is that any data appearing in column M is
the
result of a formula. How can I have the code ignore the formula and only
recognize the result of the formula.

Thanks
Pat

Findly found the source of the problem. In the event of anyone being
interested,
instead of:
If WorksheetFunction.CountA(Range("M28:M1000")) = _
WorksheetFunction.Count(Range("M28:M1000")) Then

it should be:
If WorksheetFunction.CountA(Range("M28:M1000")) 0 Then



"Pat" wrote in message
...
After having deleted the formulas in M28:M1000 the code still returns an
error. The problem seems to relate to K22. Whenever QS is replaced with a
different data the problem doses not appear.
Here is more of the code:


If Not IsEmpty(Cells(22, 11)) Then _
Cells(22, 11).Value = _
Application.Substitute(Cells(22, 11), "qs", "QS")


Selection.AutoFilter Field:=10, Criteria1:="<"

If IsEmpty(Cells(22, 11)) Then
ans = MsgBox("You must enter an Order No. If no Picking List has been
generated then enter QS instead. QS denotes Quick Sale.", vbOKOnly)
Range("K22").Select
Exit Sub
End If

If WorksheetFunction.CountA(Range("M28:M1000")) = _
WorksheetFunction.Count(Range("M28:M1000")) Then
If Cells(22, 11).Value = "QS" Then
MsgBox "There is data in column M28:M1000, find and delete
this
data "
Exit Sub
End If
End If

Hope someone can pinpoint the problem
Thanks
Pat


"Pat" wrote in message
...
When K22 contains "QS" and there is data in column M a message will
appear.

If WorksheetFunction.CountA(Range("M28:M1000")) = _
WorksheetFunction.Count(Range("M28:M1000")) Then
If Cells(22, 11).Value = "QS" Then
MsgBox "There is data in column M28:M1000, find and delete
this
data "
Exit Sub
End If

The problem I am encountering is that any data appearing in column M is
the
result of a formula. How can I have the code ignore the formula and only
recognize the result of the formula.

Thanks
Pat