On Error GoTo issue

[IM4FLSt]

Hi guys,

I have written some code that prints a worksheet in a particular way. I have included a couple error handling codes and I am having issues when it loops back through the second time. Here is a condensed version of the code that gives me the issue:

Sub Macro4()
'Change to PageBreak View
ActiveWindow.View = xlPageBreakPreview
CLColumnNum = 21
For a = 1 To 2
'[Deleted code that does not apply to this question]
On Error GoTo ExitFor:
'Adjust HPageBreaks
For c = 1 To 10
    original = ActiveSheet.HPageBreaks(c).Location.Row '<=== ERROR MESSAGE OCCURS HERE ON SECOND LOOP THROUGH a = 2. It should GoTo ExitFor:
    after1 = "B" & original + 1
    newbreakrow = Range("B:B").Find(What:=Chr(149), After:=Range(after1), _
        LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) _
        .Row
'Set new HPageBreak
    Set ActiveSheet.HPageBreaks(c).Location = Cells(newbreakrow, CLColumnNum)
'Exit For Loop
    If 1 = 2 Then
ExitFor:
On Error GoTo 0
        Exit For
    End If
Next c
'[Deleted code that does not apply to this question]
'Remove HPageBreaks
ActiveSheet.HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1
Next a
End Sub

Any ideas would be a huge help.

*Note: In this code, I have a = 1 to 2 but in the full code a = 1 to response1 where response1 is the result of an InputBox, so it could loop many more times than two.

Thanks for the help.

John

 

[Richard Schollar]

Hi John

Why don't you restrict the loo to the number of horizontal page breaks?

For i = 1 to Activesheet.HPageBreaks.Count

for example.

 

[IM4FLSt]

Ah, that is a more effective approach. Thanks for the help Richard.

John