Error Handler Only Works Once

[Bonediggler]

Hi--

I wrote some code the logic of which should do the following:

1) Loops through a column of values (inner loop)
2) Looks for 4 specific values
3) When it finds any of these 4 values, copies the entire row it is in
4) Pastes this row to another worksheet in the same workbook
5) Go back and complete the inner loop
6) When the inner loop is done, move to the next worksheet (specified via an outer loop) and do the same thing over again

I also wrote an error handler that does the following:

1) If, during the inner loop, the first specified value is not available, look for the second value. If the second value is not available, look for the third value and so on. If the fourth value is not available, go to the outer loop (next worksheet).

The problem is that the error handler works for the first instance of the inner loop (i.e. for the first worksheet), but does not work after that--it throws 'run time error 91 Object Variable or With Block variable not set'...the error that is generated when it cannot find a value...which of course it was designed to handle.

Thanks in advance for your help.

 

[SkipVought]

Hi,

I write code that SHOULD do things, but doesn't.

It's ALWAYS either incorrect syntax or incorrect logic.

Unfortunately, you have provided nothing to evaluate.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[Bonediggler]

DNIS1 = "3778"
DNIS2 = "3779"
DNIS3 = "3780"
DNIS4 = "3797"

a = Range("c1").Value
b = Range("e1").Value

On Error GoTo ErrorHandler

For k = a To b

WorksheetActivate: Worksheets(k).Activate

For i = DNIS1 To DNIS4

If i = 3781 Then i = 3797

Cells.Find(i).Activate
Set x = ActiveCell
Set y = ActiveCell.End(xlToRight)

Range(x, y).Copy

Worksheets("End").Activate
Cells.Find(i).Offset(3, 1).PasteSpecial

Worksheets(k).Activate

Nexti:
Next i


Nextk:
Next k

Exit Sub

ErrorHandler:

If i = 3778 Then GoTo Nexti
If i = 3779 Then GoTo Nexti
If i = 3780 Then GoTo Nexti
If i = 3797 Then GoTo Nextk

End Sub

 

[SkipVought]

Have you stepped thru your code?

It seem obvious that i goes from DNIS1 To DNIS4 and if it errors when i does not equal any of your DNIS values, the sub ENDS.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[SQLScholar]

I am no VBA expert - but i came accross something in VB6 that was very similar.

Answer here:

http://www.tek-tips.com/viewthread.cfm?qid=1389135&page=2

Look for the post from StrongM expalins all quite well. Maybe the same problem in VBA.



----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[Bonediggler]

Good point. I did not account for none of the 4 variables being present on the sheet. This will have to be worked into the code as well.

However, what I'm dealing with now is that values 3778, 3779, and 3780 are in the first worksheet. When it cannot find 3797 the error handler works properly by passing control to Nextk, which activates worksheet (2) in the loop.

On worksheet 2, value 3778 is not available and instead of going to the error handler which would take it to Nexti, it throws the error message and terminates the procedure.

 

[GlennUK]

Just a quick suggestion, how about using Resume instead of Goto? I would guess that using Goto would mean that the execution would still be in "Error mode" ( for want of a better description ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SQLScholar]

Glenn,

My post points to the same issue.

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[Bonediggler]

All--


Thanks for the help. I think we may have the answer!

I'll let you know if problems remain...

 

[GlennUK]

Hi Plank,

oops, didn't look at that before I posted.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SQLScholar]

Dont worry about it - i wasnt getting at you. Glad we both posted the same idea. Just wanted to make sure mine was read also along the same lines

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------