Page 1 of 2 12 LastLast
Results 1 to 10 of 13

"Run Time error '9' - Subscript out of range"

This is a discussion on "Run Time error '9' - Subscript out of range" within the Excel Questions forums, part of the Question Forums category; Hi All I am stepping through my code and I get to this line: Set HelpSheet = ThisWorkbook.Sheets(HelpSheetName) and it ...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Portsmouth.UK
    Posts
    547

    Default "Run Time error '9' - Subscript out of range"

    Hi All

    I am stepping through my code and I get to this line:

    Set HelpSheet = ThisWorkbook.Sheets(HelpSheetName)

    and it gives an error message:

    "Run Time error 9 - Subscript out of range".

    I have had a look at the help file, but dont really understand it.

    Thanks in advance for any help

    Kindest Regards
    Kindest Regards

    Peter

  2. #2
    MrExcel MVP Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,424

    Default

    Hi Peter,

    Please re-confirm the variable HelpSheetName.
    Does it have a value as string?
    Regards,

    Masaru Kaji aka Colo - Ex Microsoft MVP Since 2004 -2009

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Portsmouth.UK
    Posts
    547

    Default Re: "Run Time error '9' - Subscript out of range"

    Option Explicit
    Dim TopicCount As Integer
    Dim CurrentTopic As Integer
    Dim HelpSheet As Worksheet

    Const AppName As String = "Clares Automated Pricing System"
    Const HelpSheetName As String = "HelpSheet"
    Const HelpFormCaption As String = AppName

    String value.

    Kind Regards
    Kindest Regards

    Peter

  4. #4
    MrExcel MVP Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,424

    Default

    OK, OK. Your code works in my excel as follows.
    ...So, PLEASE check the name of the worksheet (HelpSheet),
    if any space containd.
    Eg,
    (HelpSheet )
    ( HelpSheet)

    Code:
    Option Explicit
    Dim TopicCount As Integer
    Dim CurrentTopic As Integer
    Dim HelpSheet As Worksheet
    
    Const AppName As String = "Clares Automated Pricing System"
    Const HelpSheetName As String = "HelpSheet"
    Const HelpFormCaption As String = AppName
    
    Sub test()
        Dim HelpSheet As Worksheet
        Set HelpSheet = ThisWorkbook.Sheets(HelpSheetName)
    End Sub
    Regards,

    Masaru Kaji aka Colo - Ex Microsoft MVP Since 2004 -2009

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Portsmouth.UK
    Posts
    547

    Default

    Hi Colo

    Thanks for that. I have checked my code with yours. But mine still brings the error up. I have checked and renamed the worksheet to ensure that there are no spaces in the name, either brgining and end!

    Kindest Regards
    Kindest Regards

    Peter

  6. #6
    Board Regular
    Join Date
    Aug 2003
    Location
    Maidstone, Kent UK
    Posts
    1,470

    Default

    When you get to the line that bombs out have you checked that the value of HelpSheetName is still "HelpSheet" ?

    Just wondering if somewhere esle in the code might be modifying it.

    Peter

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Portsmouth.UK
    Posts
    547

    Default Re: "Run Time error '9' - Subscript out of range"

    Hi there

    When I glide my cursor over the row of code which is highlighted in yellow:

    "Set HelpSheet = ThisWorkbook.Sheets(HelpSheetName)"

    it display the following text

    HelpSheet=Nothing,
    ThisWorkbook.Sheets(HelpSheetName)
    HelpSheetName = "HelpSheet"

    Kindest Regards
    Kindest Regards

    Peter

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Portsmouth.UK
    Posts
    547

    Default Re: "Run Time error '9' - Subscript out of range"

    Hi there

    Sorry the previous should have read:

    When I glide my cursor over the row of code which is highlighted in yellow:

    "Set HelpSheet = ThisWorkbook.Sheets ( HelpSheetName )"

    it display the following text

    HelpSheet=Nothing,
    ThisWorkbook.Sheets(HelpSheetName)
    HelpSheetName = "HelpSheet"

    Kindest Regards
    Kindest Regards

    Peter

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Portsmouth.UK
    Posts
    547

    Default Re: "Run Time error '9' - Subscript out of range"

    Hi there

    Sorry the previous should have read, ok this doesn;t like the less than or greater than brackets which subscript appears in:

    When I glide my cursor over the row of code which is highlighted in yellow:

    "Set HelpSheet = ThisWorkbook.Sheets ( HelpSheetName )"

    it display the following text

    HelpSheet=Nothing,
    ThisWorkbook.Sheets(HelpSheetName) subscript out of range
    HelpSheetName = "HelpSheet"

    Kindest Regards
    Kindest Regards

    Peter

  10. #10
    Board Regular
    Join Date
    Aug 2003
    Location
    Maidstone, Kent UK
    Posts
    1,470

    Default

    I have just added colo's code to a new spreadsheet and it works fine for me. Can you post more of the code that you are actualy running?

    Peter

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com