E
Edgar
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
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