Error

[Edgar]

HI

The following code should check to find any errors in
formulas on sheet "Email"

It works fine if there are errors on the sheet but when
there are no errors it returns the error 'Run Time error
1004' No cells found.

I thought the 2 on error statements should handle this
error but they dont seem to work.

The Sheet that I am checking has formulas on the first 125
rows and columns b, c, d but only some of the formulas
will actually work due to some if statements. Can anyone
think of any reason for this happening.

Let me know if you require any further info.

TIA

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
On Error GoTo 0
Set rng1 = rng.Offset(0, 1).Resize(,
2).SpecialCells(xlFormulas, xlErrors)
If Not rng1 Is Nothing Then
Set rng2 = Intersect(rng1.EntireRow, .Range
("A:C"))
rng2.Cut Destination:=Worksheets
("Exceptions_Report").Range("A8")
End If
End With

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

Worksheets("Exceptions_Report").Select
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

 

[Tom Ogilvy]

whoever gave you the code meant for you to do this:

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

 

[Edgar]

I have amended the code and am still getting the error -
any further suggestions?

TIA

Edgar

 

[Tom Ogilvy]

I ran this version of it:

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"))
MsgBox rng2.Address
Else
MsgBox "No cells found"
End If
End With

End Sub

on a blank sheet and a sheet with a single cell with a formula. Neither
gave an error.

Perhaps you have Break on All errors checked in the VBE under Options. If
so, you should change it to Break on Unhandled errors.

If that isn't the problem, then perhaps the problem is farther down in the
code - but the fix I suggested would handle the original problem you
described (as demonstrated above).