Error Problem
 

Hi

Thanks for the help with the last query. I have the
following code which creates an exceptions report by
checking which formulas have errors in them on
sheet "Email".

The problem is if there are no errors in the sheet it
comes up with the message "Run time error 1004, No cells
were found"

I thought that on error resume next would sort this out
but it doenst seem to work - any ideas?

Sub Exceptions_Report()
Dim rng As Range, rng1 As Range, rng2 As Range

Application.ScreenUpdating = False

With Worksheets("Email")
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
On Error Resume Next
Set rng1 = rng.Offset(0, 1).Resize(, 2).SpecialCells
(xlFormulas, xlErrors)
On Error GoTo 0
If Not rng1 Is Nothing Then
Set rng2 = Intersect(rng1.EntireRow, .Range("A:C"))
rng2.Copy Destination:=Worksheets
("Exceptions_Report").Range("A8")
End If
End With

Worksheets("Exceptions_Report").Select
Columns("A:A").HorizontalAlignment = xlLeft

With Worksheets("Exceptions_Report").PageSetup
..PrintTitleRows = "$1:$7"
..LeftMargin = Application.InchesToPoints(0.748031496062992)
..RightMargin = Application.InchesToPoints
(0.748031496062992)
..TopMargin = Application.InchesToPoints(0.984251968503937)
..BottomMargin = Application.InchesToPoints
(0.984251968503937)
..HeaderMargin = Application.InchesToPoints
(0.511811023622047)
..FooterMargin = Application.InchesToPoints
(0.511811023622047)
..CenterHorizontally = True
..Orientation = xlPortrait
End With

Application.ScreenUpdating = False
Exceptions.Show


End Sub

Error Problem
 

Edgar,

I tried your macro (through the first 'End With' statement) with a sample
pair of worksheets and could not reproduce the problem. Which line of code
is triggering the error message? Can you give us a set of sample data that
produces the error for you?

You should not need the 'On Error' statement. The statement:

Set rng1 = rng.Offset(0, 1).Resize(, 2).SpecialCells _
(xlFormulas, xlErrors)

will not trigger an error if no cells are found. You will just get a range
that equals Nothing. Testing for the range being Nothing, as you do
subsequently in your code is sufficient precaution against the case of no
cells being found.

Shockley


"Edgar" wrote in message
...
Hi

Thanks for the help with the last query. I have the
following code which creates an exceptions report by
checking which formulas have errors in them on
sheet "Email".

The problem is if there are no errors in the sheet it
comes up with the message "Run time error 1004, No cells
were found"

I thought that on error resume next would sort this out
but it doenst seem to work - any ideas?

Sub Exceptions_Report()
Dim rng As Range, rng1 As Range, rng2 As Range

Application.ScreenUpdating = False

With Worksheets("Email")
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
On Error Resume Next
Set rng1 = rng.Offset(0, 1).Resize(, 2).SpecialCells
(xlFormulas, xlErrors)
On Error GoTo 0
If Not rng1 Is Nothing Then
Set rng2 = Intersect(rng1.EntireRow, .Range("A:C"))
rng2.Copy Destination:=Worksheets
("Exceptions_Report").Range("A8")
End If
End With

Worksheets("Exceptions_Report").Select
Columns("A:A").HorizontalAlignment = xlLeft

With Worksheets("Exceptions_Report").PageSetup
.PrintTitleRows = "$1:$7"
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints
(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints
(0.984251968503937)
.HeaderMargin = Application.InchesToPoints
(0.511811023622047)
.FooterMargin = Application.InchesToPoints
(0.511811023622047)
.CenterHorizontally = True
.Orientation = xlPortrait
End With

Application.ScreenUpdating = False
Exceptions.Show


End Sub