Error
 

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

Error
 

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
--
Regards,
Tom Ogilvy


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

Error
 

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

TIA

Edgar


-----Original Message-----
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
--
Regards,
Tom Ogilvy


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


.

Error
 

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).

--
Regards,
Tom Ogilvy


"Edgar" wrote in message
...
I have amended the code and am still getting the error -
any further suggestions?

TIA

Edgar


-----Original Message-----
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
--
Regards,
Tom Ogilvy


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


.