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

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

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

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

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

If warn Then
MsgBox ("WARNING!")
End If
End Sub

--
Gary''s Student - gsnu200857


"Monk" wrote:

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

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

You could have kept your original structure and just exited the subroutine
on the first find...

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

Although I did add the optional arguments to InStr to make it case
insensitive. By the way, this code could return an incorrect result if one
of the "earlier" cells has text that contains the word "Error" in it
(especially now that I made the search case insensitive; although your
original code could also misreport an error in the same way).

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
Sub ErrorCatcher()
Dim r As Range, warn As Boolean
warn = False
Set r = Range("L2:L1250")
For Each rr In r
If InStr(rr.Value, "Error") 0 Then
warn = True
End If
Next

If warn Then
MsgBox ("WARNING!")
End If
End Sub

--
Gary''s Student - gsnu200857


"Monk" wrote:

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

Another one:

Option Explicit
Sub Testme()

dim myRng as range
dim myCount as long
dim myWord as string

myWord = "Error"
'or if the error can be with other words.
myWord = "*Error*"

set myrng = worksheets("Sheet9999").range("l2:l1250")

mycount = application.countif(myrng, myWord)

if mycount = 0 then
'do nothing ' or msgbox "not found"
else
msgbox myWord & " was found " & mycount & " times."
end if

end sub

When I do this kind of stuff, I like to put the message in an adjacent cell in
nice big bold letters:

=if(countif(l2:l1250,"error")=0,"","Errors in the data!")
or
=if(countif(l2:l1250,"*error*")=0,"","Errors in the data!")



Monk wrote:

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

--

Dave Peterson

This will give you something to work with. Put the code in the worksheet
code module by right clicking the sheet name tab, then select "View Code".
Copy the code below and paste into the code window. Any change in the sheet
will then look in column L for the word "Error".

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Set c = ActiveSheet.Cells.Find("Error", LookIn:=xlValues)
If Not c Is Nothing Then
If Not Intersect(c, Range("L2:L1250")) Is Nothing Then
MsgBox "Error in Range(""" & c.Address & """)"
End If
End If
End Sub



"Monk" wrote in message
...
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

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

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