T
Tim Zych
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
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