I am trying to run a page setup macro. The first part is simple. It does a
page setup as you can see.
The 2nd part is where I am having problems. The macro only seems to work
when I start out in normal page view. Before the 2nd part is executed (I put
a stop in while testing), I must change to page break view, and then
complete the macro.

Weird thing is, if I set the sheet to page break view before starting the
macro, the loop continues one more than the count of the HPageBreaks. In
other words, if I have 4 HPageBreaks, the loop completes 4 and then loops
through a 5th time.

Consequently, it returns a runtime error "9: Subscript out of range" for the
5th loop since it can't refer to HPageBreaks(5) when HPageBreaks.Count = 4.

In the immediate window during the error:

?wks.HPageBreaks.Count
4
?i
5

Anyone know what the heck is going on? Am I setting HPageBreaks incorrectly?

Thanks.

Sub DoPageSetup()

Dim i As Integer, wks As Worksheet, cell As Range
Set wks = ActiveSheet

'Only works if I start out in normal page view mode ''''''

With wks.PageSetup
.PrintTitleRows = "$1:$4"
.CenterFooter = "&P of &N"
.RightFooter = "&D &T"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.75)
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

'Then I must view page breaks to get this part to work '''''''

For i = 1 To wks.HPageBreaks.Count
Set cell = wks.HPageBreaks(i).Location
Set cell = GetNextNonblankCellUp(cell)
Set wks.HPageBreaks(i).Location = cell
Next

End Sub

'returns the next non-blank cell up from a starting range position
Function GetNextNonblankCellUp(ByVal pcell As Range) As Range
Dim cell As Range
Set cell = pcell
Do
Set cell = cell.Offset(-1)
Loop Until Len(cell.Formula) 0
Set GetNextNonblankCellUp = cell
End Function

Hi Tim:

My experience with HPageBreaks collection is that it is buggy. It has to
somehow be initialized (by printing or previewing or scrolling through)
before it is "set". Of course, this applies to VPageBreaks as well.

I have not seen an elegant solution that gets around this. There are some
Excel 4 macros that apparently work better, but I don't have them handy.

Regards,

Vasant.

"Tim Zych" wrote in message
...
I am trying to run a page setup macro. The first part is simple. It does a
page setup as you can see.
The 2nd part is where I am having problems. The macro only seems to work
when I start out in normal page view. Before the 2nd part is executed (I
put
a stop in while testing), I must change to page break view, and then
complete the macro.

Weird thing is, if I set the sheet to page break view before starting the
macro, the loop continues one more than the count of the HPageBreaks. In
other words, if I have 4 HPageBreaks, the loop completes 4 and then loops
through a 5th time.

Consequently, it returns a runtime error "9: Subscript out of range" for
the
5th loop since it can't refer to HPageBreaks(5) when HPageBreaks.Count =
4.

In the immediate window during the error:

?wks.HPageBreaks.Count
4
?i
5

Anyone know what the heck is going on? Am I setting HPageBreaks
incorrectly?

Thanks.

Sub DoPageSetup()

Dim i As Integer, wks As Worksheet, cell As Range
Set wks = ActiveSheet

'Only works if I start out in normal page view mode ''''''

With wks.PageSetup
.PrintTitleRows = "$1:$4"
.CenterFooter = "&P of &N"
.RightFooter = "&D &T"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.75)
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

'Then I must view page breaks to get this part to work '''''''

For i = 1 To wks.HPageBreaks.Count
Set cell = wks.HPageBreaks(i).Location
Set cell = GetNextNonblankCellUp(cell)
Set wks.HPageBreaks(i).Location = cell
Next

End Sub

'returns the next non-blank cell up from a starting range position
Function GetNextNonblankCellUp(ByVal pcell As Range) As Range
Dim cell As Range
Set cell = pcell
Do
Set cell = cell.Offset(-1)
Loop Until Len(cell.Formula) 0
Set GetNextNonblankCellUp = cell
End Function

Just found some code from Myrna Larson which may be helpful:

http://google.com/groups?selm=d6L3Nz...u5Q8%404ax.com


Vasant


"Tim Zych" wrote in message
...
I am trying to run a page setup macro. The first part is simple. It does a
page setup as you can see.
The 2nd part is where I am having problems. The macro only seems to work
when I start out in normal page view. Before the 2nd part is executed (I
put
a stop in while testing), I must change to page break view, and then
complete the macro.

Weird thing is, if I set the sheet to page break view before starting the
macro, the loop continues one more than the count of the HPageBreaks. In
other words, if I have 4 HPageBreaks, the loop completes 4 and then loops
through a 5th time.

Consequently, it returns a runtime error "9: Subscript out of range" for
the
5th loop since it can't refer to HPageBreaks(5) when HPageBreaks.Count =
4.

In the immediate window during the error:

?wks.HPageBreaks.Count
4
?i
5

Anyone know what the heck is going on? Am I setting HPageBreaks
incorrectly?

Thanks.

Sub DoPageSetup()

Dim i As Integer, wks As Worksheet, cell As Range
Set wks = ActiveSheet

'Only works if I start out in normal page view mode ''''''

With wks.PageSetup
.PrintTitleRows = "$1:$4"
.CenterFooter = "&P of &N"
.RightFooter = "&D &T"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.75)
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

'Then I must view page breaks to get this part to work '''''''

For i = 1 To wks.HPageBreaks.Count
Set cell = wks.HPageBreaks(i).Location
Set cell = GetNextNonblankCellUp(cell)
Set wks.HPageBreaks(i).Location = cell
Next

End Sub

'returns the next non-blank cell up from a starting range position
Function GetNextNonblankCellUp(ByVal pcell As Range) As Range
Dim cell As Range
Set cell = pcell
Do
Set cell = cell.Offset(-1)
Loop Until Len(cell.Formula) 0
Set GetNextNonblankCellUp = cell
End Function

Thanks for the link Vasant. I ended up using my approach (more polished than
what I posted) since it seemed to work.

I appreciate the validation of the unusual behavior from the info in the
link.

tim


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Just found some code from Myrna Larson which may be helpful:

http://google.com/groups?selm=d6L3Nz...u5Q8%404ax.com


Vasant


"Tim Zych" wrote in message
...
I am trying to run a page setup macro. The first part is simple. It does
a
page setup as you can see.
The 2nd part is where I am having problems. The macro only seems to work
when I start out in normal page view. Before the 2nd part is executed (I
put
a stop in while testing), I must change to page break view, and then
complete the macro.

Weird thing is, if I set the sheet to page break view before starting
the
macro, the loop continues one more than the count of the HPageBreaks. In
other words, if I have 4 HPageBreaks, the loop completes 4 and then
loops
through a 5th time.

Consequently, it returns a runtime error "9: Subscript out of range" for
the
5th loop since it can't refer to HPageBreaks(5) when HPageBreaks.Count =
4.

In the immediate window during the error:

?wks.HPageBreaks.Count
4
?i
5

Anyone know what the heck is going on? Am I setting HPageBreaks
incorrectly?

Thanks.

Sub DoPageSetup()

Dim i As Integer, wks As Worksheet, cell As Range
Set wks = ActiveSheet

'Only works if I start out in normal page view mode ''''''

With wks.PageSetup
.PrintTitleRows = "$1:$4"
.CenterFooter = "&P of &N"
.RightFooter = "&D &T"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.75)
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

'Then I must view page breaks to get this part to work '''''''

For i = 1 To wks.HPageBreaks.Count
Set cell = wks.HPageBreaks(i).Location
Set cell = GetNextNonblankCellUp(cell)
Set wks.HPageBreaks(i).Location = cell
Next

End Sub

'returns the next non-blank cell up from a starting range position
Function GetNextNonblankCellUp(ByVal pcell As Range) As Range
Dim cell As Range
Set cell = pcell
Do
Set cell = cell.Offset(-1)
Loop Until Len(cell.Formula) 0
Set GetNextNonblankCellUp = cell
End Function