Run-time error'9' subscript out of range
Posted
by Chris
on Stack Overflow
See other posts from Stack Overflow
or by Chris
Published on 2010-04-16T22:23:48Z
Indexed on
2010/04/16
22:33 UTC
Read the original article
Hit count: 394
The error occurs when I rename the file. I need to be able to the macro automatically recognise the change in the file name and apply it to the macro. Is there any way to do this without having to manually change it each time which will no work for what I need this to do
Sub OccurenceSort()
'
' OccurenceSort Macro
' Macro recorded 4/9/2010 by Chris Greenlee
'
' Keyboard Shortcut: Ctrl+o
'
Sheets("Occurences").Select
Range("A1:D58").Select
Range("D58").Activate
Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("Chart").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Occurences!R2C2:R12C2"
End Sub
Sub OccurenceByValue()
'
' OccurenceByValue Macro
' Macro recorded 4/9/2010 by Chris Greenlee
'
' Keyboard Shortcut: Ctrl+v
'
ActiveWindow.Visible = False
Windows("QA Project - Automated Charts v1.1.xls").Activate
Sheets("Occurences").Select
Range("A1:D58").Select
Range("D58").Activate
Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("Chart").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Values = "=Occurences!R2C3:R12C3"
End Sub
Sub OccurencesByPercentIncreaseToScore()
'
' OccurencesByPercentIncreaseToScore Macro
' Macro recorded 4/9/2010 by Chris Greenlee
'
' Keyboard Shortcut: Ctrl+p
'
ActiveWindow.Visible = False
Windows("QA Project - Automated Charts v1.1.xls").Activate
Sheets("Occurences").Select
Range("A1:D58").Select
Range("D58").Activate
Selection.Sort Key1:=Range("D2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("Chart").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Values = "=Occurences!R2C4:R12C4"
End Sub
The problem occurs with this line Windows("QA Project - Automated Charts v1.1.xls").Activate
© Stack Overflow or respective owner