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: 1
Default Run-time error 9 (Subscript out of range)

Hello,
This is my first attempt at writing VBA I'm trying to write some code
to delete some columns in a worksheet but I'm getting a run-time error
9. I tried to use the Excel macro to record a macro to delete and
format the worksheets but the code failed on
"Sheets("MovedOut").Select". I have tried to determine why I received
the error but was not able to.

Any help is appreciated with my code structure is appreciated.


Dim iSheetCount As Integer
Dim iSheet As Integer
Dim ws As Worksheets
Dim wb As Workbook

'Formatting MovedOut Orders Worksheet

Sheets("MovedOut").Select
ActiveCell.Offset(0, 2).Columns("A:D").EntireColumn.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(*
""-""_);_(@_)"
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[84]C)"
ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWindow.SmallScroll ToRight:=3
ActiveCell.Offset(0, 1).Columns("A:D").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWindow.SmallScroll ToRight:=6
ActiveCell.Offset(0, 1).Columns("A:O").EntireColumn.Select
ActiveWindow.SmallScroll ToRight:=3
ActiveCell.Offset(0, 15).Columns("A:A").EntireColumn.Select
ActiveWindow.LargeScroll ToRight:=-1
ActiveCell.Offset(0, -15).Columns("A:P").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Offset(0, 4).Columns("A:A").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Columns("A:D").EntireColumn.Select
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(*
""-""_);_(@_)"
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[84]C)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:D1"), Type:=
_
xlFillDefault
ActiveCell.Range("A1:D1").Select
ActiveCell.Offset(0, 4).Columns("A:A").EntireColumn.Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Select

On Error Resume Next


ws("FieldCycleTimes").Select
Selection.AutoFilter Field:=7, Criteria1:="Mr James Favaron"


ws("ForecastNextMonth").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

ws("FieldPipeline").Select
Selection.AutoFilter Field:=5, Criteria1:="Mr James Favaron"

ws("MovedOut").Select
Selection.AutoFilter Field:=5, Criteria1:="Mr James Favaron"

ws("MovedIn").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

ws("Forecast").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

ws("Actual").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

iSheetCount = ActiveWorkbook.Worksheets.Count
For iSheet = 1 To iSheetCount
Worksheets(iSheet).Activate
MsgBox Worksheets(iSheet).Name
Next iSheet


End Sub


--
Lizz45ie
------------------------------------------------------------------------
Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410
View this thread: http://www.excelforum.com/showthread...hreadid=479597

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 107
Default Run-time error 9 (Subscript out of range)
Don't mean to be patronising, but is your sheet really called
"MovedOut"?
Not "Moved Out" or Moveout"?

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,119
Default Run-time error 9 (Subscript out of range)
hopefully this will give you a little help... It avoids the activecell and
the offsets you are using which for the most part you really don't need.

with Sheets("MovedOut")
.Select 'this is optional butnecessary if you want to use the active cell
.Columns("A:D").EntireColumn.Delete Shift:=xlToLeft
.Columns("A:A").EntireColumn.NumberFormat = "_(* #,##0_);_(* (#,##0);_(*
""-""_);_(@_)"
end with


--
HTH...

Jim Thomlinson


"Lizz45ie" wrote:


Hello,
This is my first attempt at writing VBA I'm trying to write some code
to delete some columns in a worksheet but I'm getting a run-time error
9. I tried to use the Excel macro to record a macro to delete and
format the worksheets but the code failed on
"Sheets("MovedOut").Select". I have tried to determine why I received
the error but was not able to.

Any help is appreciated with my code structure is appreciated.


Dim iSheetCount As Integer
Dim iSheet As Integer
Dim ws As Worksheets
Dim wb As Workbook

'Formatting MovedOut Orders Worksheet

Sheets("MovedOut").Select
ActiveCell.Offset(0, 2).Columns("A:D").EntireColumn.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(*
""-""_);_(@_)"
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[84]C)"
ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWindow.SmallScroll ToRight:=3
ActiveCell.Offset(0, 1).Columns("A:D").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWindow.SmallScroll ToRight:=6
ActiveCell.Offset(0, 1).Columns("A:O").EntireColumn.Select
ActiveWindow.SmallScroll ToRight:=3
ActiveCell.Offset(0, 15).Columns("A:A").EntireColumn.Select
ActiveWindow.LargeScroll ToRight:=-1
ActiveCell.Offset(0, -15).Columns("A:P").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Offset(0, 4).Columns("A:A").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Columns("A:D").EntireColumn.Select
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(*
""-""_);_(@_)"
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[84]C)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:D1"), Type:=
_
xlFillDefault
ActiveCell.Range("A1:D1").Select
ActiveCell.Offset(0, 4).Columns("A:A").EntireColumn.Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Select

On Error Resume Next


ws("FieldCycleTimes").Select
Selection.AutoFilter Field:=7, Criteria1:="Mr James Favaron"


ws("ForecastNextMonth").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

ws("FieldPipeline").Select
Selection.AutoFilter Field:=5, Criteria1:="Mr James Favaron"

ws("MovedOut").Select
Selection.AutoFilter Field:=5, Criteria1:="Mr James Favaron"

ws("MovedIn").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

ws("Forecast").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

ws("Actual").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

iSheetCount = ActiveWorkbook.Worksheets.Count
For iSheet = 1 To iSheetCount
Worksheets(iSheet).Activate
MsgBox Worksheets(iSheet).Name
Next iSheet


End Sub


--
Lizz45ie
------------------------------------------------------------------------
Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410
View this thread: http://www.excelforum.com/showthread...hreadid=479597


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 170
Default Run-time error 9 (Subscript out of range)
If you have more than one workbook open, the following may prevent the
subscript out of range error:
(assumes MovedOut is in the same workbook as the code)

Set wb = ThisWorkbook
wb.Sheets("MovedOut")

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Lizz45ie" wrote in
message ...

Hello,
This is my first attempt at writing VBA I'm trying to write some code
to delete some columns in a worksheet but I'm getting a run-time error
9. I tried to use the Excel macro to record a macro to delete and
format the worksheets but the code failed on
"Sheets("MovedOut").Select". I have tried to determine why I received
the error but was not able to.

Any help is appreciated with my code structure is appreciated.


Dim iSheetCount As Integer
Dim iSheet As Integer
Dim ws As Worksheets
Dim wb As Workbook

'Formatting MovedOut Orders Worksheet

Sheets("MovedOut").Select
ActiveCell.Offset(0, 2).Columns("A:D").EntireColumn.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(*
""-""_);_(@_)"
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[84]C)"
ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWindow.SmallScroll ToRight:=3
ActiveCell.Offset(0, 1).Columns("A:D").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWindow.SmallScroll ToRight:=6
ActiveCell.Offset(0, 1).Columns("A:O").EntireColumn.Select
ActiveWindow.SmallScroll ToRight:=3
ActiveCell.Offset(0, 15).Columns("A:A").EntireColumn.Select
ActiveWindow.LargeScroll ToRight:=-1
ActiveCell.Offset(0, -15).Columns("A:P").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Offset(0, 4).Columns("A:A").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Columns("A:D").EntireColumn.Select
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(*
""-""_);_(@_)"
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[84]C)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:D1"), Type:=
_
xlFillDefault
ActiveCell.Range("A1:D1").Select
ActiveCell.Offset(0, 4).Columns("A:A").EntireColumn.Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Select

On Error Resume Next


ws("FieldCycleTimes").Select
Selection.AutoFilter Field:=7, Criteria1:="Mr James Favaron"


ws("ForecastNextMonth").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

ws("FieldPipeline").Select
Selection.AutoFilter Field:=5, Criteria1:="Mr James Favaron"

ws("MovedOut").Select
Selection.AutoFilter Field:=5, Criteria1:="Mr James Favaron"

ws("MovedIn").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

ws("Forecast").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

ws("Actual").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

iSheetCount = ActiveWorkbook.Worksheets.Count
For iSheet = 1 To iSheetCount
Worksheets(iSheet).Activate
MsgBox Worksheets(iSheet).Name
Next iSheet


End Sub


--
Lizz45ie
------------------------------------------------------------------------
Lizz45ie's Profile:
http://www.excelforum.com/member.php...o&userid=23410
View this thread: http://www.excelforum.com/showthread...hreadid=479597



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 AccessHelp Excel Programming 1 September 30th 05 05:10 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
Run time error 9 (subscript out of range) Nathaniel Tigere Excel Programming 2 August 5th 03 11:12 AM


All times are GMT +1. The time now is 10:35 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement