Run-time error 9 (Subscript out of range)

L

Lizz45ie

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
 
J

JakeyC

Don't mean to be patronising, but is your sheet really called
"MovedOut"?
Not "Moved Out" or Moveout"?
 
J

Jim Thomlinson

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 said:
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
Click to expand...
 
G

George Nicholson

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 said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Wrong number of arguments or invalid property assignment 1
Different Results from the Same Macro 3
Problem with Macro 7
Run-Time error '9' Subscript out of Range 7
macro code doesnt work in command button 4
Run-time error `91': Object variable . . . not set 2
A Better Way to Reorganize Data? 5
variable number of rows. 3

Top