Run Time Error 9 Subscript out of Range

**Petrolcb41** · 11-26-2010, 07:13 PM

Can anyone tell me why this macro (which I use to sort rows) is generating an error message?

Sub sortstores()
Sheets("OER Dashboard Report").Unprotect Password:="Grimmer"
Range("C11:Y89").Sort Key1:=Range("F1"), _
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Sheets("OER Dashboard Report").Protect Password:="Grimmer"
End Sub

**Richard Buttrey** · 11-26-2010, 07:28 PM

Hi,

Your sort key is outside the sort range. Try F11 instead.

HTH

**Petrolcb41** · 11-26-2010, 08:27 PM

Richard,

The error still seems to be occuring. When I remove the unlock/lock aspect of the macro, it works fine however it creates an error when the password aspects are present. Any idea why this may be happening?

I basically need the above macro to, when executed, unlock the sheet, run the sort macro, then re-lock the sheet. The sheet's name is OER Dashboard Report and the password is Grimmer

**Richard Buttrey** · 11-26-2010, 08:43 PM

Hi,

If the OER Dashboard Report isn't the active sheet at the time you invoke the sort then you will encounter an error. Try a fully defined sort range. i.e.

Sub sortstores()
Sheets("OER Dashboard Report").Unprotect Password:="Grimmer"
Sheets("OER Dashboard Report").Range("C11:Y89").Sort Key1:=Sheets("OER Dashboard Report").Range("F1"), _
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Sheets("OER Dashboard Report").Protect Password:="Grimmer"
End Sub

or perhaps more elegantly with a WITH statement

Sub sortstores()
   With Sheets("OER Dashboard Report")
      .Unprotect Password:="Grimmer"
      .Range("C11:Y89").Sort Key1:=.Range("F1"), _
      Order1:=xlDescending, Header:=xlGuess, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom, _
      DataOption1:=xlSortTextAsNumbers
      .Protect Password:="Grimmer"
   End With
End Sub

HTH