Article ID: 210663 - View products that this article applies to.
This article was previously published under Q210663
When you use the Location property or Location.Address of vertical or horizontal page breaks in a Microsoft Visual Basic for Applications macro, you may receive the following error message:
Run-time error '9':
Subscript out of range
This problem may occur if the following conditions are true:
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
To prevent this problem, add code to select the last cell used in the worksheet before the code uses the Location property of horizontal or vertical page breaks. For example, use the following code to select the end cell, use the Location property, and then reselect the original active cell:
After the Location property is calculated, you may again select the original active cell. If you use code to scroll between the first and last cell, or select the last cell and immediately reselect the starting cell, the error may still occur. The screen must redraw and the Location property be calculated for the workaround to be effective. If you use the above code with
to prevent screen redraw, the problem still occurs.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
If you use the Count method with the VPageBreaks or HPageBreaks property, you may receive a result of zero. This occurs under the conditions listed in the "Cause" section. If a page break is visible, the Count method may give the correct answer. The Count method for vertical or horizontal page breaks may give the expected result if a page break is near the visible part of the workbook window. The workaround given earlier can be used to obtain the expected count.
Page breaks that are to the right of the workbook window or below the workbook window may enable the Count method to work and that page break to be located, if the distance from the window to the page break is less than one-half the distance between page breaks.
For more information about how to trap errors in a macro, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/213637/ )How to use "On Error" to handle errors in a macro
Article ID: 210663 - Last Review: January 23, 2007 - Revision: 5.0
Contact us for more help
Connect with Answer Desk for expert help.