ExcelBanter - runtime error 9 subscript out of range #3

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - runtime error 9 subscript out of range #3 (https://www.excelbanter.com/excel-programming/395961-runtime-error-9-subscript-out-range-3-a.html)

runtime error 9 subscript out of range #3

I tried the if block to make sure there is a pagebreak in x. There is a
pagebreak in x because I saw it in the immediate window so the problem isn't
that there isn't any pagebreaks although it is a nice error check for that.

Sub VOD_11x17_Page_Setup()

'This is the new page set up without column B for sheets + VOD_v2.
Dim x As Integer
Dim I As Integer
Dim K As Integer
Dim J As String
Dim C As Range
Dim PageNumber As Long
Dim SubTotalRow As Long
Dim Test As Boolean
Dim Row1 As Integer

Dim AC_Sheet As Worksheet
Dim AW As Workbook
Dim AW_Name As String
Dim UsedRange1 As Range
Dim UsedRows1 As Long
Dim UsedCol1 As Long
Dim SubTotalRows As Variant
Dim RowsPerPage As Long

Application.ActiveSheet.UsedRange
Set AC_Sheet = Application.ActiveSheet
Set AW = Application.ActiveWorkbook
AW_Name = AW.name
Set UsedRange1 = AC_Sheet.UsedRange
UsedRows1 = UsedRange1.Rows.Count
UsedCol1 = UsedRange1.Columns.Count

SubTotalRows = GetSubTotalRows()

' nNetwork = getPrinter(nError)
' If nNetwork = 0 Then
'
' MsgBox "the command to print has an error."
' MsgBox nError
' Else

' Application.ActivePrinter = "\\martinezfs1-bay\Ca-Martinez-94C on
Ne0" & nNetwork & ":"
' End If

Application.ActivePrinter = "\\martinezfs1-bay\CA-Martinez-94C on Ne02:"
PS411x17
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintArea = ""
.PrintTitleRows = "$1:$11"
.PrintTitleColumns = ""
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveSheet.DisplayPageBreaks = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks

ActiveWindow.View = xlNormalView
x = ActiveSheet.HPageBreaks.Count
If x 2 Then
With ActiveSheet.HPageBreaks
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With
End If
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
' With ActiveSheet.HPageBreaks
' RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
' End With
K = 1
PageNumber = 1
Row1 = 0

For I = 0 To UBound(SubTotalRows)
SubTotalRow = SubTotalRows(I)
If Row1 = 0 Then
Row1 = ActiveSheet.HPageBreaks(PageNumber).Location.Row
End If
If SubTotalRow Row1 Then
Set ActiveSheet.HPageBreaks(PageNumber).Location =
Cells(SubTotalRows(I - 1) + 1, 1)
Row1 = SubTotalRows(I - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

' If SubTotalRow Row1 Then
' ActiveWindow.SelectedSheets.HPageBreaks.Add
Befo=Cells(SubTotalRows(I - 1) + 1, 1)
' Row1 = SubTotalRows(I - 1) + RowsPerPage
' PageNumber = PageNumber + 11
' End If
Next I

For I = 1 To x
If x < ActiveSheet.HPageBreaks.Count Then
I = I - (ActiveSheet.HPageBreaks.Count - x)
K = I + (ActiveSheet.HPageBreaks.Count - x)
x = ActiveSheet.HPageBreaks.Count
End If
J = ActiveSheet.HPageBreaks(K).Location.Address
Row1 = Range(J).Row

Set ActiveSheet.HPageBreaks(K).Location = Cells(Row1, 1)
K = K + 1
Next I

Application.ScreenUpdating = True
ActiveWindow.View = xlNormalView
Range(FirstDataCell).Activate
Range("A1").Activate

End Sub

do you have any other ideas?

thanks,

runtime error 9 subscript out of range #3

Janis, I can't see anything wrong with that section of the code. But
apparently, there is something that prevents the system from seeing one or
both of the Item references. Each of them should represent a cell reference
(which you knew) and
if x is registering a count greater than one (1) then the math should
produce the number of lines between the page breaks. So, have you tried:

RowsPerPage = ActiveSheet.HPageBreaks(2).Row - ActiveSheet.HPageBreaks(1).Row

Comment out the With statement and see if the above line works.

"Janis" wrote:

I tried the if block to make sure there is a pagebreak in x. There is a
pagebreak in x because I saw it in the immediate window so the problem isn't
that there isn't any pagebreaks although it is a nice error check for that.

Sub VOD_11x17_Page_Setup()

'This is the new page set up without column B for sheets + VOD_v2.
Dim x As Integer
Dim I As Integer
Dim K As Integer
Dim J As String
Dim C As Range
Dim PageNumber As Long
Dim SubTotalRow As Long
Dim Test As Boolean
Dim Row1 As Integer

Dim AC_Sheet As Worksheet
Dim AW As Workbook
Dim AW_Name As String
Dim UsedRange1 As Range
Dim UsedRows1 As Long
Dim UsedCol1 As Long
Dim SubTotalRows As Variant
Dim RowsPerPage As Long

Application.ActiveSheet.UsedRange
Set AC_Sheet = Application.ActiveSheet
Set AW = Application.ActiveWorkbook
AW_Name = AW.name
Set UsedRange1 = AC_Sheet.UsedRange
UsedRows1 = UsedRange1.Rows.Count
UsedCol1 = UsedRange1.Columns.Count

SubTotalRows = GetSubTotalRows()

' nNetwork = getPrinter(nError)
' If nNetwork = 0 Then
'
' MsgBox "the command to print has an error."
' MsgBox nError
' Else

' Application.ActivePrinter = "\\martinezfs1-bay\Ca-Martinez-94C on
Ne0" & nNetwork & ":"
' End If

Application.ActivePrinter = "\\martinezfs1-bay\CA-Martinez-94C on Ne02:"
PS411x17
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintArea = ""
.PrintTitleRows = "$1:$11"
.PrintTitleColumns = ""
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveSheet.DisplayPageBreaks = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks

ActiveWindow.View = xlNormalView
x = ActiveSheet.HPageBreaks.Count
If x 2 Then
With ActiveSheet.HPageBreaks
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With
End If
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
' With ActiveSheet.HPageBreaks
' RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
' End With
K = 1
PageNumber = 1
Row1 = 0

For I = 0 To UBound(SubTotalRows)
SubTotalRow = SubTotalRows(I)
If Row1 = 0 Then
Row1 = ActiveSheet.HPageBreaks(PageNumber).Location.Row
End If
If SubTotalRow Row1 Then
Set ActiveSheet.HPageBreaks(PageNumber).Location =
Cells(SubTotalRows(I - 1) + 1, 1)
Row1 = SubTotalRows(I - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

' If SubTotalRow Row1 Then
' ActiveWindow.SelectedSheets.HPageBreaks.Add
Befo=Cells(SubTotalRows(I - 1) + 1, 1)
' Row1 = SubTotalRows(I - 1) + RowsPerPage
' PageNumber = PageNumber + 11
' End If
Next I

For I = 1 To x
If x < ActiveSheet.HPageBreaks.Count Then
I = I - (ActiveSheet.HPageBreaks.Count - x)
K = I + (ActiveSheet.HPageBreaks.Count - x)
x = ActiveSheet.HPageBreaks.Count
End If
J = ActiveSheet.HPageBreaks(K).Location.Address
Row1 = Range(J).Row

Set ActiveSheet.HPageBreaks(K).Location = Cells(Row1, 1)
K = K + 1
Next I

Application.ScreenUpdating = True
ActiveWindow.View = xlNormalView
Range(FirstDataCell).Activate
Range("A1").Activate

End Sub

do you have any other ideas?

thanks,

runtime error 9 subscript out of range #3

I think I found another mistake. I will repost it later.

"Janis" wrote:

I tried the if block to make sure there is a pagebreak in x. There is a
pagebreak in x because I saw it in the immediate window so the problem isn't
that there isn't any pagebreaks although it is a nice error check for that.

Sub VOD_11x17_Page_Setup()

'This is the new page set up without column B for sheets + VOD_v2.
Dim x As Integer
Dim I As Integer
Dim K As Integer
Dim J As String
Dim C As Range
Dim PageNumber As Long
Dim SubTotalRow As Long
Dim Test As Boolean
Dim Row1 As Integer

Dim AC_Sheet As Worksheet
Dim AW As Workbook
Dim AW_Name As String
Dim UsedRange1 As Range
Dim UsedRows1 As Long
Dim UsedCol1 As Long
Dim SubTotalRows As Variant
Dim RowsPerPage As Long

Application.ActiveSheet.UsedRange
Set AC_Sheet = Application.ActiveSheet
Set AW = Application.ActiveWorkbook
AW_Name = AW.name
Set UsedRange1 = AC_Sheet.UsedRange
UsedRows1 = UsedRange1.Rows.Count
UsedCol1 = UsedRange1.Columns.Count

SubTotalRows = GetSubTotalRows()

' nNetwork = getPrinter(nError)
' If nNetwork = 0 Then
'
' MsgBox "the command to print has an error."
' MsgBox nError
' Else

' Application.ActivePrinter = "\\martinezfs1-bay\Ca-Martinez-94C on
Ne0" & nNetwork & ":"
' End If

Application.ActivePrinter = "\\martinezfs1-bay\CA-Martinez-94C on Ne02:"
PS411x17
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintArea = ""
.PrintTitleRows = "$1:$11"
.PrintTitleColumns = ""
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveSheet.DisplayPageBreaks = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks

ActiveWindow.View = xlNormalView
x = ActiveSheet.HPageBreaks.Count
If x 2 Then
With ActiveSheet.HPageBreaks
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With
End If
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
' With ActiveSheet.HPageBreaks
' RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
' End With
K = 1
PageNumber = 1
Row1 = 0

For I = 0 To UBound(SubTotalRows)
SubTotalRow = SubTotalRows(I)
If Row1 = 0 Then
Row1 = ActiveSheet.HPageBreaks(PageNumber).Location.Row
End If
If SubTotalRow Row1 Then
Set ActiveSheet.HPageBreaks(PageNumber).Location =
Cells(SubTotalRows(I - 1) + 1, 1)
Row1 = SubTotalRows(I - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

' If SubTotalRow Row1 Then
' ActiveWindow.SelectedSheets.HPageBreaks.Add
Befo=Cells(SubTotalRows(I - 1) + 1, 1)
' Row1 = SubTotalRows(I - 1) + RowsPerPage
' PageNumber = PageNumber + 11
' End If
Next I

For I = 1 To x
If x < ActiveSheet.HPageBreaks.Count Then
I = I - (ActiveSheet.HPageBreaks.Count - x)
K = I + (ActiveSheet.HPageBreaks.Count - x)
x = ActiveSheet.HPageBreaks.Count
End If
J = ActiveSheet.HPageBreaks(K).Location.Address
Row1 = Range(J).Row

Set ActiveSheet.HPageBreaks(K).Location = Cells(Row1, 1)
K = K + 1
Next I

Application.ScreenUpdating = True
ActiveWindow.View = xlNormalView
Range(FirstDataCell).Activate
Range("A1").Activate

End Sub

do you have any other ideas?

thanks,

runtime error 9 subscript out of range #3

Left something out.

RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row - _
ActiveSheet.HPageBreaks(1).Location.Row

"JLGWhiz" wrote:

Janis, I can't see anything wrong with that section of the code. But
apparently, there is something that prevents the system from seeing one or
both of the Item references. Each of them should represent a cell reference
(which you knew) and
if x is registering a count greater than one (1) then the math should
produce the number of lines between the page breaks. So, have you tried:

RowsPerPage = ActiveSheet.HPageBreaks(2).Row - ActiveSheet.HPageBreaks(1).Row

Comment out the With statement and see if the above line works.

"Janis" wrote:

I tried the if block to make sure there is a pagebreak in x. There is a
pagebreak in x because I saw it in the immediate window so the problem isn't
that there isn't any pagebreaks although it is a nice error check for that.

Sub VOD_11x17_Page_Setup()

'This is the new page set up without column B for sheets + VOD_v2.
Dim x As Integer
Dim I As Integer
Dim K As Integer
Dim J As String
Dim C As Range
Dim PageNumber As Long
Dim SubTotalRow As Long
Dim Test As Boolean
Dim Row1 As Integer

Dim AC_Sheet As Worksheet
Dim AW As Workbook
Dim AW_Name As String
Dim UsedRange1 As Range
Dim UsedRows1 As Long
Dim UsedCol1 As Long
Dim SubTotalRows As Variant
Dim RowsPerPage As Long

Application.ActiveSheet.UsedRange
Set AC_Sheet = Application.ActiveSheet
Set AW = Application.ActiveWorkbook
AW_Name = AW.name
Set UsedRange1 = AC_Sheet.UsedRange
UsedRows1 = UsedRange1.Rows.Count
UsedCol1 = UsedRange1.Columns.Count

SubTotalRows = GetSubTotalRows()

' nNetwork = getPrinter(nError)
' If nNetwork = 0 Then
'
' MsgBox "the command to print has an error."
' MsgBox nError
' Else

' Application.ActivePrinter = "\\martinezfs1-bay\Ca-Martinez-94C on
Ne0" & nNetwork & ":"
' End If

Application.ActivePrinter = "\\martinezfs1-bay\CA-Martinez-94C on Ne02:"
PS411x17
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintArea = ""
.PrintTitleRows = "$1:$11"
.PrintTitleColumns = ""
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveSheet.DisplayPageBreaks = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks

ActiveWindow.View = xlNormalView
x = ActiveSheet.HPageBreaks.Count
If x 2 Then
With ActiveSheet.HPageBreaks
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With
End If
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row
' With ActiveSheet.HPageBreaks
' RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
' End With
K = 1
PageNumber = 1
Row1 = 0

For I = 0 To UBound(SubTotalRows)
SubTotalRow = SubTotalRows(I)
If Row1 = 0 Then
Row1 = ActiveSheet.HPageBreaks(PageNumber).Location.Row
End If
If SubTotalRow Row1 Then
Set ActiveSheet.HPageBreaks(PageNumber).Location =
Cells(SubTotalRows(I - 1) + 1, 1)
Row1 = SubTotalRows(I - 1) + RowsPerPage
PageNumber = PageNumber + 1
End If

' If SubTotalRow Row1 Then
' ActiveWindow.SelectedSheets.HPageBreaks.Add
Befo=Cells(SubTotalRows(I - 1) + 1, 1)
' Row1 = SubTotalRows(I - 1) + RowsPerPage
' PageNumber = PageNumber + 11
' End If
Next I

For I = 1 To x
If x < ActiveSheet.HPageBreaks.Count Then
I = I - (ActiveSheet.HPageBreaks.Count - x)
K = I + (ActiveSheet.HPageBreaks.Count - x)
x = ActiveSheet.HPageBreaks.Count
End If
J = ActiveSheet.HPageBreaks(K).Location.Address
Row1 = Range(J).Row

Set ActiveSheet.HPageBreaks(K).Location = Cells(Row1, 1)
K = K + 1
Next I

Application.ScreenUpdating = True
ActiveWindow.View = xlNormalView
Range(FirstDataCell).Activate
Range("A1").Activate

End Sub

do you have any other ideas?

thanks,