Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > run time error #9, subscript out of range
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 360
Default run time error #9, subscript out of range
The line it stops on is on the line it sets the RowsPerPage Variable.

ActiveSheet.DisplayPageBreaks = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks

'ActiveWindow.View = xlNormalView
x = ActiveSheet.HPageBreaks.Count
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row

The RowsPerPage variable is declared as a long. Is it running out of memory?


I noticed on my CPU usage is around 98%. Do I need a bigger cpu? My
computer is
Intel(R) Core(TM) 2 cpu, , 2.13 GHz 0.99GB ram
tia,


Janis
------the whole sub------------------------


Sub V_11x17_Page_Setup()


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()


Application.ActivePrinter = "\\bay\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
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row

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
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

------------


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 360
Default run time error #9, subscript out of range
I don't have a pc at home. I'll have to tell you for sure tomorrow. I
think there are multiple horizontal page breaks.

"Jim Thomlinson" wrote:

Do you have multiple horizontal page breaks? What is the value of x just
prior to executing that line of code. I cleaned up the line a bit below but
it is essentially the same as yours...

With ActiveSheet.HPageBreaks
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With

As for needing a faster computer that probably won't help. Anything to do
with setting up printing is just slow in XL. You can use xl4 macros to speed
thing up but they are a bit of work to write. One easy thing you can do is to
remove all of the setup properties that are the default values such as .Zoom
= false

PS. Nice looking code :-)
--
HTH...

Jim Thomlinson


"Janis" wrote:

The line it stops on is on the line it sets the RowsPerPage Variable.

ActiveSheet.DisplayPageBreaks = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks

'ActiveWindow.View = xlNormalView
x = ActiveSheet.HPageBreaks.Count
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row

The RowsPerPage variable is declared as a long. Is it running out of memory?


I noticed on my CPU usage is around 98%. Do I need a bigger cpu? My
computer is
Intel(R) Core(TM) 2 cpu, , 2.13 GHz 0.99GB ram
tia,


Janis
------the whole sub------------------------


Sub V_11x17_Page_Setup()


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()


Application.ActivePrinter = "\\bay\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
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row

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
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

------------


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 360
Default run time error #9, subscript out of range
CAn I get rid of the page breaks and let excel break it? The problem is this
routine is connected with a sub-total routine and the sub-totals aren't
getting subtotalled and my boss has to do it mannually on a lot of sheets
:-(.

"Jim Thomlinson" wrote:

Do you have multiple horizontal page breaks? What is the value of x just
prior to executing that line of code. I cleaned up the line a bit below but
it is essentially the same as yours...

With ActiveSheet.HPageBreaks
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With

As for needing a faster computer that probably won't help. Anything to do
with setting up printing is just slow in XL. You can use xl4 macros to speed
thing up but they are a bit of work to write. One easy thing you can do is to
remove all of the setup properties that are the default values such as .Zoom
= false

PS. Nice looking code :-)
--
HTH...

Jim Thomlinson


"Janis" wrote:

The line it stops on is on the line it sets the RowsPerPage Variable.

ActiveSheet.DisplayPageBreaks = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks

'ActiveWindow.View = xlNormalView
x = ActiveSheet.HPageBreaks.Count
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row

The RowsPerPage variable is declared as a long. Is it running out of memory?


I noticed on my CPU usage is around 98%. Do I need a bigger cpu? My
computer is
Intel(R) Core(TM) 2 cpu, , 2.13 GHz 0.99GB ram
tia,


Janis
------the whole sub------------------------


Sub V_11x17_Page_Setup()


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()


Application.ActivePrinter = "\\bay\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
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row

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
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

------------


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 360
Default run time error #9, subscript out of range
TOMORROW I'LL RUN it in debug mode and find out the value of x.

"Jim Thomlinson" wrote:

Do you have multiple horizontal page breaks? What is the value of x just
prior to executing that line of code. I cleaned up the line a bit below but
it is essentially the same as yours...

With ActiveSheet.HPageBreaks
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With

As for needing a faster computer that probably won't help. Anything to do
with setting up printing is just slow in XL. You can use xl4 macros to speed
thing up but they are a bit of work to write. One easy thing you can do is to
remove all of the setup properties that are the default values such as .Zoom
= false

PS. Nice looking code :-)
--
HTH...

Jim Thomlinson


"Janis" wrote:

The line it stops on is on the line it sets the RowsPerPage Variable.

ActiveSheet.DisplayPageBreaks = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks

'ActiveWindow.View = xlNormalView
x = ActiveSheet.HPageBreaks.Count
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row

The RowsPerPage variable is declared as a long. Is it running out of memory?


I noticed on my CPU usage is around 98%. Do I need a bigger cpu? My
computer is
Intel(R) Core(TM) 2 cpu, , 2.13 GHz 0.99GB ram
tia,


Janis
------the whole sub------------------------


Sub V_11x17_Page_Setup()


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()


Application.ActivePrinter = "\\bay\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
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row

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
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

------------


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 360
Default run time error #9, subscript out of range
I do find you as most helpful. Many thanks. I ran the macro on a smaller
sheet and it goes past that place. It does have more than 10 page breaks.
Do you know the limit on how many pages Excel could format like this? I
think we are reaching the limit.

"Jim Thomlinson" wrote:

Do you have multiple horizontal page breaks? What is the value of x just
prior to executing that line of code. I cleaned up the line a bit below but
it is essentially the same as yours...

With ActiveSheet.HPageBreaks
RowsPerPage = .Item(2).Location.Row - .Item(1).Location.Row
End With

As for needing a faster computer that probably won't help. Anything to do
with setting up printing is just slow in XL. You can use xl4 macros to speed
thing up but they are a bit of work to write. One easy thing you can do is to
remove all of the setup properties that are the default values such as .Zoom
= false

PS. Nice looking code :-)
--
HTH...

Jim Thomlinson


"Janis" wrote:

The line it stops on is on the line it sets the RowsPerPage Variable.

ActiveSheet.DisplayPageBreaks = True
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks

'ActiveWindow.View = xlNormalView
x = ActiveSheet.HPageBreaks.Count
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row

The RowsPerPage variable is declared as a long. Is it running out of memory?


I noticed on my CPU usage is around 98%. Do I need a bigger cpu? My
computer is
Intel(R) Core(TM) 2 cpu, , 2.13 GHz 0.99GB ram
tia,


Janis
------the whole sub------------------------


Sub V_11x17_Page_Setup()


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()


Application.ActivePrinter = "\\bay\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
RowsPerPage = ActiveSheet.HPageBreaks(2).Location.Row -
ActiveSheet.HPageBreaks(1).Location.Row

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
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

------------


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
run-time error '9': subscript out of range [email protected] uk Excel Discussion (Misc queries) 4 December 8th 09 10:27 PM
run time error 9, subscript out of range [email protected] Excel Programming 6 July 7th 06 02:35 AM
run-time error '9': Subscript out of range jerredjohnson[_2_] Excel Programming 1 March 8th 06 07:11 PM
Run time error 9 : Subscript out of range JAtz_DA_WAY Excel Discussion (Misc queries) 6 August 29th 05 08:26 PM
Run time error '9' Subscript out of range Tina Excel Programming 1 August 25th 03 02:05 AM


All times are GMT +1. The time now is 04:44 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement