Error 9

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
Hi all

Why my code stop on the second error "Subscript out of range"?
Code:
Dim LC As Integer, FB As Integer
Dim Ws As Worksheet
Application.ScreenUpdating = False
For Each Ws In ActiveWorkbook.Worksheets
On Error GoTo nextsh
LC = Ws.Range([A1], Ws.UsedRange).Columns.Count
ActiveWindow.View = xlPageBreakPreview

FB = Ws.VPageBreaks(1).Location.Column

'here i have other commands

nextsh:
Next Ws
FB = Ws.VPageBreaks(1).Location.Column
If first sheet give me error here, code jump to the errorhandler(nextsh). But if this part of code give error second time i got orror on the screen and code stop.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
no

If my code check the first sheet and got error because sheet dont have pagebreakes and this is ok. Other code ignored and i want check next sheet. But now in the same place where i check pagebreake my code stop and not ignore error like i want "On Error GoTo nextsh".
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,912
Office Version
  1. 365
Platform
  1. Windows
Why not check for page breaks instead of relying on error handling?
Code:
If Ws.VPageBreaks.Count<>0 Then
   ' do stuff
Else
   Msgbox Ws.Name & " has no vertical page breaks."
End If
 

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
Yes Norie you right
And ty erik to for your link
in my case simple Err.Clear not working but solution is:
Code:
On Error Resume Next
FB = Ws.VPageBreaks(1).Location.Column 
if err.number = 9 then goto nextsh

nextsh: 
Err.Clear 
Next Ws

Ty all
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,455
Members
410,612
Latest member
MrACED
Top