ExcelBanter - On Error GoTo

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - On Error GoTo (https://www.excelbanter.com/excel-programming/305626-error-goto.html)

I am using an On Error GoTo statement within a loop, however, the On Error GoTo statement only works once when crossed in the loop. Is there a way around this?

My code is:
Do Until acounter = ActiveWorkbook.Sheets.Count
sales = -Range("B14").Value
gp = -Range("B23").Value
cp = -Range("B47").Value
store = Left(Right(ActiveSheet.Name, 6), 5)
Windows(zfile).Activate
Sheets("Post Project Financials").Select
On Error GoTo sheet2
Cells.Find(What:=store, After:=ActiveCell, LookIn:=xlValues,LookAt:=
xlWhole,SearchOrder:=xlByRows,SearchDirection:=xlN ext,MatchCase:=False)
.Activate
storerange = ActiveCell.Address
sheetresume:
Range(storerange).Select
Selection.Offset(0, 7).Activate
ActiveCell.Value = sales

acounter = acounter + 1
Windows(afile).Activate
ActiveSheet.Next.Select
Loop

Exit Sub
sheet2:
Sheets("Post Project Financials (2)").Select
Cells.Find(What:=store, After:=ActiveCell, LookIn:=xlValues,LookAt:=
xlWhole,SearchOrder:=xlByRows,SearchDirection:=xlN ext,MatchCase:=False)
.Activate
storerange = ActiveCell.Address
GoTo sheetresume

Stan,

To clear the error handling state, you need to use Resume rather
than Goto in your error handling code. Change

GoTo sheetresume

to

Resume sheetresume

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"StanJ" wrote in message
...
I am using an On Error GoTo statement within a loop, however,
the On Error GoTo statement only works once when crossed in the
loop. Is there a way around this?

My code is:
Do Until acounter = ActiveWorkbook.Sheets.Count
sales = -Range("B14").Value
gp = -Range("B23").Value
cp = -Range("B47").Value
store = Left(Right(ActiveSheet.Name, 6), 5)
Windows(zfile).Activate
Sheets("Post Project Financials").Select
On Error GoTo sheet2
Cells.Find(What:=store, After:=ActiveCell,
LookIn:=xlValues,LookAt:=

xlWhole,SearchOrder:=xlByRows,SearchDirection:=xlN ext,MatchCase:=
False)
.Activate
storerange = ActiveCell.Address
sheetresume:
Range(storerange).Select
Selection.Offset(0, 7).Activate
ActiveCell.Value = sales

acounter = acounter + 1
Windows(afile).Activate
ActiveSheet.Next.Select
Loop

Exit Sub
sheet2:
Sheets("Post Project Financials (2)").Select
Cells.Find(What:=store, After:=ActiveCell,
LookIn:=xlValues,LookAt:=

xlWhole,SearchOrder:=xlByRows,SearchDirection:=xlN ext,MatchCase:=
False)
.Activate
storerange = ActiveCell.Address
GoTo sheetresume