Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Run-time error '9' (2000)

    I have the following script in a module in my personal.xls file. When I run it, I get an error as follows: "Run-time error '9': Subscript out of range". Any help?

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time error '9' (2000)

    You didn't post any code.

    Could you please post the code that is causing the error, so we can see it?
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run-time error '9' (2000)

    There's no "following", but, unencombered with knowledge, I would say that you mispelled a named range or a sheet name. If that doesn't help, press debug and post the line that it doesn't like.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Run-time error '9' (2000)

    ... which is in effect the same as using a name that doesn't exist, which could also be the problem.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Run-time error '9' (2000)

    Sorry:

    Sub HEADERS_FOOTERS()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
    Application.StatusBar = "Changing header/footer in " & ws.Name
    With ws.PageSetup
    .LeftHeader = "Mountaineer Gas Company"
    .CenterHeader = "Page &P of &N"
    .RightHeader = Sheets("Sheet1").Range("A2") & vbLf & "Printed &D &T"
    .LeftFooter = "File : " & ActiveWorkbook.Path
    .RightFooter = "&A" & vbLf & "Page &P of &N"
    End With
    Next ws
    Set ws = Nothing
    Application.StatusBar = False

    End Sub

    The compilation error highlights the "Tifgtheader language.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Run-time error '9' (2000)

    Assuming you meant to type "RightHeader"* change that line to

    .RightHeader = ws.Range("A2") & vbLf & "Printed &D &T"

    otherwise you are attampting to treat each "ws" as if it named "Sheet1".

    (*I'm a dreadful typist.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time error '9' (2000)

    I am assuming this line (The compilation error highlights the "Tifgtheader language.) should read The compilation error highlights the "Rigtheader language.

    If that is the case, the only possible problem I see is that you may not have a sheet that is named Sheet1.

    Other than that, it should work.

    One thing you may want to add at the end of the code is

    Application.ScreenUpdating = True

    You turned it off at the start of the code, so you should turn it back on at the end.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Run-time error '9' (2000)

    John-that did it! Thanks!
    One other question-how do I get the Left Footer on the same line as the first line of the Right Footer?
    Thanks again,
    Jeff

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Run-time error '9' (2000)

    Put a vbLf in front of it.

    .LeftHeader = vbLf & "Mountaineer Gas Company"

    Oops, I misread the question in haste. Isn't the way you want it the default behavior?
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Run-time error '9' (2000)

    No-the footer.

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Run-time error '9' (2000)

    Put a vbLf after it. Also you may want to consider using:

    .LeftFooter = "File : " & ActiveWorkbook.FullName & vbLf
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run-time error '9' (2000)

    <P ID="edit" class=small>(Edited by SammyB on 20-Jan-03 13:10. )</P>> how do I get the Left Footer on the same line as the first line of the Right Footer
    Dosn't look like you can when the left footer is one line and the right is two.
    How about a compromise, two lines in each:

    Edited -- after I read John's post, of course you can: just insert a blank line:
    <pre>Option Explicit

    Sub HEADERS_FOOTERS()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
    Application.StatusBar = "Changing header/footer in " & ws.Name
    With ws.PageSetup
    .LeftHeader = "Mountaineer Gas Company"
    .CenterHeader = "Page &P of &N"
    .RightHeader = ws.Range("A2") & vbCr & "Printed &D &T"
    .LeftFooter = "File : " & ActiveWorkbook.Path & vbCr
    .RightFooter = "&A" & vbCr & "Page &P of &N"
    End With
    Next ws
    Set ws = Nothing
    Application.StatusBar = False
    Application.ScreenUpdating = True
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Run-time error '9' (2000)

    Thanks to all. As for my last question, I put the following at the end of the line dealing with the left footer " & vbLf"
    Jeff

Posting Permissions

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