|
On Error GoTo Question
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: -- Helping Is always a good thing |
On Error GoTo Question
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 "QuietMan" wrote: 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: -- Helping Is always a good thing |
On Error GoTo Question
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 "QuietMan" wrote: 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: -- Helping Is always a good thing |
On Error GoTo Question
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" wrote: 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: -- Helping Is always a good thing |
On Error GoTo Question
Thanks...I'll try it now
-- Helping Is always a good thing "Patrick Molloy" wrote: 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" wrote: 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: -- Helping Is always a good thing |
| All times are GMT +1. The time now is 02:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com