On Error GoTo Question

[QuietMan]

Does anyone know why this will not go to No_Profit...1st error check works
fine but second gives me an error

Thanks

Range(Cells(x - 1, 1), Cells(Last_Entity, 1)).Select
On Error GoTo No_Rev
Selection.Find(What:="REVENUE").Activate
Rev_Top = ActiveCell.Row + 1
Selection.Find(What:="TOTAL REVENUE").Activate
Rev_Bottom = ActiveCell.Row - 1
No_Rev:
On Error GoTo No_Profit
Selection.Find(What:="DEPT PROFIT").Activate
Profit_Top = ActiveCell.Row + 1
Selection.Find(What:="TOTAL DEPT PROFIT").Activate
Profit_Bottom = ActiveCell.Row - 1
No_Profit:

 

[Patrick Molloy]

try

Dim found As Range
Set found = Range(Cells(x - 1, 1), Cells(Last_Entity,
1)).Find("REVENUE").Activate
If Not found Is Nothing Then
Rev_Top = found.Row + 1
Set found = Range(Cells(x - 1, 1), Cells(Last_Entity, 1)).Find("TOTAL
REVENUE").Activate
If Not found Is Nothing Then
Rev_Bottom = found.Row - 1
End If
End If

Set found = Range(Cells(x - 1, 1), Cells(Last_Entity, 1)).Find("DEPT PROFIT")
If Not found Is Nothing Then
Profit_Top = found.Row + 1
Set found = Range(Cells(x - 1, 1), Cells(Last_Entity, 1)).Find("TOTAL
DEPT PROFIT")
If Not found Is Nothing Then
Profit_Bottom = found.Row - 1
End If
End If

 

[Mike H]

Hi,

You can't do that.

When the first error is raised, execution transfers to the line No_Rev:

The error hander is still active when the second error occurs, and therefore
the second error is not trapped by the second On Error statement.


have a look at Chip Pearson's site

http://www.cpearson.com/excel/ErrorHandling.htm

Mike

 

[Patrick Molloy]

on error pushes a return address onto the stack, this needs to be poped
before another on error can be set

he's your code, with the fix


Range(Cells(x - 1, 1), Cells(Last_Entity, 1)).Select
On Error GoTo error1
Selection.Find(What:="REVENUE").Activate
Rev_Top = ActiveCell.Row + 1
Selection.Find(What:="TOTAL REVENUE").Activate
Rev_Bottom = ActiveCell.Row - 1
No_Rev:
On Error GoTo error2
Selection.Find(What:="DEPT PROFIT").Activate
Profit_Top = ActiveCell.Row + 1
Selection.Find(What:="TOTAL DEPT PROFIT").Activate
Profit_Bottom = ActiveCell.Row - 1
No_Profit:
''' blah more code
Exit Sub
error1: Resume No_Rev
error2: Resume No_Profit
END SUB

 

[QuietMan]

Thanks...I'll try it now