Error handling within loop

Resolved · Low Priority · Version Standard

Thu 13 Mar 2008, 12:38 Delegate Samantha said...

Samantha has attended:
Excel VBA Intro Intermediate course

Error handling within loop

i am getting in a pickle with error handling and am getting debug when running these within a loop. please can you help?

my coding is:

On Error GoTo theend

Range("A1").Select
'find the word "Continued"
Cells.Find(What:="Continued", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

'this will activate a procedure to format the "continued" data
GoTo Openitagain

theend:

so i keep going until the system can no longer find "Continued" and i expect that when it cant, it will go to "theend". but instead it debugs.
i presume this is because i keep opening the on error goto the end and not closing it down?

but where can i close it down and what code do i use? i tried within the "openitagain" area and put a resume or resume next but this just goes to the "theend" which isnt helpful

can you please help?

thanks
sam

Fri 28 Mar 2008, 15:25 Trainer Roy said...

RE: error handling within loop

Hi Samantha,

You shouldn't be using error handling in this case, as the situation is entirely under program control. Error handling is for things that are outside your control like trying to open a workbook that's been renamed, moved or deleted.

The Range.Find method returns the special value 'Nothing' if it can't find the What argument. You can check for this in your loop condition or with an If statement. See the online Help page for Range.Find (and the Example code).

Also:
-- If you're looking for a literal value, then LookIn should :=xlValues.
-- You shouldn't need to use a GoTo -- this generally bad coding practice. Just call the necessary procedure, which can operate on ActiveCell.

Hope this helps
/Roy

Training courses