Outer Loop Terminating Prematurely

[Bonediggler]

Hi--

This is another question related to the code posted in the "Error Handler Only Works Once" thread.

I implemented the suggestions provided regarding the error handler and made some additional necessary tweaks to the code.

The problem I am having now is that the outer loop goes to "exit sub" after the 3rd round instead of passing in the next value.

Here is the code (minus variable assignments):

On Error GoTo ErrorHandler

For k = a To b

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

If k = a Then p = 3
If k = q Then p = 4
If k = b Then p = 5
If k = n Then p = 6
If k = m Then p = 7
If k = g Then p = 8
If k = u Then p = 9

Cells.Find(i).Offset(p, 1).PasteSpecial

Worksheets(k).Activate

Nexti:
Next i

Nextk:
Next k


Exit Sub

ErrorHandler:

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

End Sub

 

[SkipVought]

Have you stepped thru your code?

Put in a break and observe what's happening.

faq707-4594

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]

Hi Skip--

I have. In fact I read your article before posting this thread.

Stepping through the code is the baffling part. I watch all the variables adjust as they should in the Watch Window as the loops execute and every thing runs normally.

However, after the third outer loop (it should do 7 loops), command goes to Nextk as it should (I check to make sure k has the correct variable assignment and it does) and instead of cycling back to the top it goes to exit sub.

This is what has me stumped...

 

[SkipVought]

At the point you exit prematurely, what is the value of b?

<aside>
BTW, I think that it's a poor idea to CHANGE the For...Next bounds (i in your case). You ought to use a Do...Loop instead. VB is forgiving. Other lanugages are not. Not a biggie, just a preference.
</aside>

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]

Not sure if that was a rhetorical question or not but you're a genius!

I had asigned 2 variables to b.


Thanks again for the help.

 

[SkipVought]

I just HATE Premature ......ation! So embarrassing! ;-)

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!}

 

[fumei]

I would also like to comment on the use of the If statements.

        If k = a Then p = 3
        If k = q Then p = 4
        If k = b Then p = 5
        If k = n Then p = 6
        If k = m Then p = 7
        If k = g Then p = 8
        If k = u Then p = 9

Every one of those instructions is executed. Seven individual instructions. If k = a, the other instructions are still executed. When testing multiple values for the same variable, it is better to use Select Case.

Select Case k
  Case a
     p = 3
  Case q
     p = 4
  Case b
     p = 5
  Case 
     n = 6
  Case a
     m = 7
  Case g
     p = 8
  Case u
     p = 9
End Select

That is ONE instruction, but multiple possible results. It checks the value of k. The If statements check True/False, not value. If k = a...then p = 3....and that's it.

faq219-2884

Gerry
My paintings and sculpture

 

[firefytr]

Select Case, whilst still being a better suited method for this application I agree, will step through each value until a match Case is found. So there is the possiblity of still processing each Case instance, much like the If/Then statements.

Also, try to stay away from both Select and Activate, they're absolutely horrible memory hogs. I'd also recommend a Select Case statement for the error handling at the end of the routine.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci