| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Question on "On Error GoTo skip" | Excel Discussion (Misc queries) | |||
| On error GoTo Question | Excel Programming | |||
| Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
| On Error Goto doesn't goto | Excel Programming | |||
| On Error Goto doesn't goto | Excel Programming | |||
Hybrid Mode
