There is also the CountIf method which can be sometimes very useful.

Sub CheckForError()

If Application.CountIf(Range("L2:L1250"), "Error") 0 Then

MsgBox "There is an error in the range L2:L1250 somewhere!"

End If

End Sub

Mishell

"Rick Rothstein" wrote in message
...
Thanks, but there is an even more "beautiful improvement" available. I
can't believe I forgot about the Find method which JLGWhiz's post reminded
me of (than JLGWhiz). Here is a much better solution...

Sub CheckForError()
If Not Worksheets("Sheet2").Range("L2:L1250").Find("Error ", LookAt:= _
xlWhole, MatchCase:=False) Is Nothing Then MsgBox "There is an error!"
End Sub

--
Rick (MVP - Excel)


"Gary''s Student" wrote in
message ...
A beautiful improvement1
--
Gary''s Student - gsnu200857


"Rick Rothstein" wrote:

Give this macro a try...

Sub CheckForError()
Dim Contents As String
Contents = Join(WorksheetFunction.Transpose(Range("L2:L1250") ),
Chr(1))
If InStr(1, Contents, Chr(1) & "Error" & Chr(1), vbTextCompare) Then
MsgBox "There is an error in the range L2:L1250 somewhere!"
End If
End Sub

--
Rick (MVP - Excel)


"Monk" wrote in message
...
Thanks. How could I amend this so that the message box will only
appear
once,
irrespective of how many times the "error" text appears in the column.
At
the
moment the box appears each time the "error" value appears. Thanks in
advance

"Gary''s Student" wrote:

Sub ErrorCatcher()
Dim r As Range
Set r = Range("L2:L1250")
For Each rr In r
If InStr(rr.Value, "Error") 0 Then
MsgBox ("WARNING monk")
End If
Next
End Sub

--
Gary''s Student - gsnu200857


"Monk" wrote:

I would like a message box to appear if the text "Error" appears in
any
cell
in the range L2:L1250. Any assistance with a macro would be
appreciated.
Thanks
Monk