Closed Thread
Results 1 to 10 of 10

out of range error

  1. #1
    Matilda
    Guest

    out of range error

    Help please! Why am I getting a Runtime error 9 subscript out of range error
    on this statement?
    Workbooks("filename.xls").Worksheets("Sheet1").Select

    Many TIAs

  2. #2
    Dave Peterson
    Guest

    Re: out of range error

    Is there a workbook named Filename.xls open?
    If yes, does that workbook have a worksheet named Sheet1?

    And if yes to both, is that workbook the active workbook--you can only select a
    worksheet on the active workbook.

    Workbooks("filename.xls").Select
    Workbooks("filename.xls").Worksheets("Sheet1").Select

    Or maybe:

    application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("a1"), _
    scroll:=true

    would be sufficient.

    Matilda wrote:
    >
    > Help please! Why am I getting a Runtime error 9 subscript out of range error
    > on this statement?
    > Workbooks("filename.xls").Worksheets("Sheet1").Select
    >
    > Many TIAs


    --

    Dave Peterson

  3. #3
    Matilda
    Guest

    Re: out of range error

    Thanks, Dave. Sorry for the lack of information. Yes, 2nd workbook open,
    tried to activate it and select it thus:
    Workbooks("filename.xls").Activate
    Sheets("Sheet1").Select
    and got the exact same error.
    I was trying to refer to the system label for the worksheet rather than the
    user defined string.
    I want to take a string from workbook 1 and search for its match in workbook 2

    "Dave Peterson" wrote:

    > Is there a workbook named Filename.xls open?
    > If yes, does that workbook have a worksheet named Sheet1?
    >
    > And if yes to both, is that workbook the active workbook--you can only select a
    > worksheet on the active workbook.
    >
    > Workbooks("filename.xls").Select
    > Workbooks("filename.xls").Worksheets("Sheet1").Select
    >
    > Or maybe:
    >
    > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("a1"), _
    > scroll:=true
    >
    > would be sufficient.
    >
    > Matilda wrote:
    > >
    > > Help please! Why am I getting a Runtime error 9 subscript out of range error
    > > on this statement?
    > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > >
    > > Many TIAs

    >
    > --
    >
    > Dave Peterson
    >


  4. #4
    Dave Peterson
    Guest

    Re: out of range error

    system label = Codename
    (the name you see in the VBE project explorer?)

    dim wks as worksheet
    with workbooks("Filename.xls")
    .activate
    for each wks in .worksheets
    if lcase(wks.codename) = "sheet1" then
    wks.select
    exit for
    end if
    next wks
    end with



    Matilda wrote:
    >
    > Thanks, Dave. Sorry for the lack of information. Yes, 2nd workbook open,
    > tried to activate it and select it thus:
    > Workbooks("filename.xls").Activate
    > Sheets("Sheet1").Select
    > and got the exact same error.
    > I was trying to refer to the system label for the worksheet rather than the
    > user defined string.
    > I want to take a string from workbook 1 and search for its match in workbook 2
    >
    > "Dave Peterson" wrote:
    >
    > > Is there a workbook named Filename.xls open?
    > > If yes, does that workbook have a worksheet named Sheet1?
    > >
    > > And if yes to both, is that workbook the active workbook--you can only select a
    > > worksheet on the active workbook.
    > >
    > > Workbooks("filename.xls").Select
    > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > >
    > > Or maybe:
    > >
    > > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("a1"), _
    > > scroll:=true
    > >
    > > would be sufficient.
    > >
    > > Matilda wrote:
    > > >
    > > > Help please! Why am I getting a Runtime error 9 subscript out of range error
    > > > on this statement?
    > > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > > >
    > > > Many TIAs

    > >
    > > --
    > >
    > > Dave Peterson
    > >


    --

    Dave Peterson

  5. #5
    Matilda
    Guest

    Re: out of range error

    Dave, I want to have your grandchildren !!
    XXXX

    "Dave Peterson" wrote:

    > system label = Codename
    > (the name you see in the VBE project explorer?)
    >
    > dim wks as worksheet
    > with workbooks("Filename.xls")
    > .activate
    > for each wks in .worksheets
    > if lcase(wks.codename) = "sheet1" then
    > wks.select
    > exit for
    > end if
    > next wks
    > end with
    >
    >
    >
    > Matilda wrote:
    > >
    > > Thanks, Dave. Sorry for the lack of information. Yes, 2nd workbook open,
    > > tried to activate it and select it thus:
    > > Workbooks("filename.xls").Activate
    > > Sheets("Sheet1").Select
    > > and got the exact same error.
    > > I was trying to refer to the system label for the worksheet rather than the
    > > user defined string.
    > > I want to take a string from workbook 1 and search for its match in workbook 2
    > >
    > > "Dave Peterson" wrote:
    > >
    > > > Is there a workbook named Filename.xls open?
    > > > If yes, does that workbook have a worksheet named Sheet1?
    > > >
    > > > And if yes to both, is that workbook the active workbook--you can only select a
    > > > worksheet on the active workbook.
    > > >
    > > > Workbooks("filename.xls").Select
    > > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > > >
    > > > Or maybe:
    > > >
    > > > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("a1"), _
    > > > scroll:=true
    > > >
    > > > would be sufficient.
    > > >
    > > > Matilda wrote:
    > > > >
    > > > > Help please! Why am I getting a Runtime error 9 subscript out of range error
    > > > > on this statement?
    > > > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > > > >
    > > > > Many TIAs
    > > >
    > > > --
    > > >
    > > > Dave Peterson
    > > >

    >
    > --
    >
    > Dave Peterson
    >


  6. #6
    Dave Peterson
    Guest

    Re: out of range error

    It's a cookbook!!!!!!!!!!!

    (line from old Outer Limits TV show)

    Matilda wrote:
    >
    > Dave, I want to have your grandchildren !!
    > XXXX
    >
    > "Dave Peterson" wrote:
    >
    > > system label = Codename
    > > (the name you see in the VBE project explorer?)
    > >
    > > dim wks as worksheet
    > > with workbooks("Filename.xls")
    > > .activate
    > > for each wks in .worksheets
    > > if lcase(wks.codename) = "sheet1" then
    > > wks.select
    > > exit for
    > > end if
    > > next wks
    > > end with
    > >
    > >
    > >
    > > Matilda wrote:
    > > >
    > > > Thanks, Dave. Sorry for the lack of information. Yes, 2nd workbook open,
    > > > tried to activate it and select it thus:
    > > > Workbooks("filename.xls").Activate
    > > > Sheets("Sheet1").Select
    > > > and got the exact same error.
    > > > I was trying to refer to the system label for the worksheet rather than the
    > > > user defined string.
    > > > I want to take a string from workbook 1 and search for its match in workbook 2
    > > >
    > > > "Dave Peterson" wrote:
    > > >
    > > > > Is there a workbook named Filename.xls open?
    > > > > If yes, does that workbook have a worksheet named Sheet1?
    > > > >
    > > > > And if yes to both, is that workbook the active workbook--you can only select a
    > > > > worksheet on the active workbook.
    > > > >
    > > > > Workbooks("filename.xls").Select
    > > > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > > > >
    > > > > Or maybe:
    > > > >
    > > > > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("a1"), _
    > > > > scroll:=true
    > > > >
    > > > > would be sufficient.
    > > > >
    > > > > Matilda wrote:
    > > > > >
    > > > > > Help please! Why am I getting a Runtime error 9 subscript out of range error
    > > > > > on this statement?
    > > > > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > > > > >
    > > > > > Many TIAs
    > > > >
    > > > > --
    > > > >
    > > > > Dave Peterson
    > > > >

    > >
    > > --
    > >
    > > Dave Peterson
    > >


    --

    Dave Peterson

  7. #7
    Matilda
    Guest

    Re: out of range error

    Sorry Dave, I just can't get it to work. Had a few days break to see if a
    fresh start helps but no deal. I have a heap of code that does work, I just
    can't get it to open the appropriate worksheet to work on!
    It should be simple ... can you comment your code for me so I can see why it
    isn't doing what I want it to do?

    two workbooks
    workbook1 has valueIwantTo Find In Workbook2
    workbook2 has n sheets, match will be in one of them
    need to open wkbk2 and run search routine (which works fine)

    Sorry to be a pain



    "Dave Peterson" wrote:

    > system label = Codename
    > (the name you see in the VBE project explorer?)
    >
    > dim wks as worksheet
    > with workbooks("Filename.xls")
    > .activate
    > for each wks in .worksheets
    > if lcase(wks.codename) = "sheet1" then
    > wks.select
    > exit for
    > end if
    > next wks
    > end with
    >
    >
    >
    > Matilda wrote:
    > >
    > > Thanks, Dave. Sorry for the lack of information. Yes, 2nd workbook open,
    > > tried to activate it and select it thus:
    > > Workbooks("filename.xls").Activate
    > > Sheets("Sheet1").Select
    > > and got the exact same error.
    > > I was trying to refer to the system label for the worksheet rather than the
    > > user defined string.
    > > I want to take a string from workbook 1 and search for its match in workbook 2
    > >
    > > "Dave Peterson" wrote:
    > >
    > > > Is there a workbook named Filename.xls open?
    > > > If yes, does that workbook have a worksheet named Sheet1?
    > > >
    > > > And if yes to both, is that workbook the active workbook--you can only select a
    > > > worksheet on the active workbook.
    > > >
    > > > Workbooks("filename.xls").Select
    > > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > > >
    > > > Or maybe:
    > > >
    > > > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("a1"), _
    > > > scroll:=true
    > > >
    > > > would be sufficient.
    > > >
    > > > Matilda wrote:
    > > > >
    > > > > Help please! Why am I getting a Runtime error 9 subscript out of range error
    > > > > on this statement?
    > > > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > > > >
    > > > > Many TIAs
    > > >
    > > > --
    > > >
    > > > Dave Peterson
    > > >

    >
    > --
    >
    > Dave Peterson
    >


  8. #8
    Dave Peterson
    Guest

    Re: out of range error

    Ok.

    'declare a worksheet variable
    dim wks as worksheet

    'start with the other workbook
    with workbooks("Filename.xls")
    'if you want to select, that workbook has to active
    .activate
    'loop through all the worksheets in that filename.xls workbook
    for each wks in .worksheets
    'if the codename--the name you see in the project explorer
    'Sheet1(nameuserseesontab)
    if lcase(wks.codename) = "sheet1" then
    'is equal to sheet1, we found it, so select it
    wks.select
    'and get out
    exit for
    end if
    next wks
    end with

    But maybe I misunderstood what "system label" meant.

    Matilda wrote:
    >
    > Sorry Dave, I just can't get it to work. Had a few days break to see if a
    > fresh start helps but no deal. I have a heap of code that does work, I just
    > can't get it to open the appropriate worksheet to work on!
    > It should be simple ... can you comment your code for me so I can see why it
    > isn't doing what I want it to do?
    >
    > two workbooks
    > workbook1 has valueIwantTo Find In Workbook2
    > workbook2 has n sheets, match will be in one of them
    > need to open wkbk2 and run search routine (which works fine)
    >
    > Sorry to be a pain
    >
    > "Dave Peterson" wrote:
    >
    > > system label = Codename
    > > (the name you see in the VBE project explorer?)
    > >
    > > dim wks as worksheet
    > > with workbooks("Filename.xls")
    > > .activate
    > > for each wks in .worksheets
    > > if lcase(wks.codename) = "sheet1" then
    > > wks.select
    > > exit for
    > > end if
    > > next wks
    > > end with
    > >
    > >
    > >
    > > Matilda wrote:
    > > >
    > > > Thanks, Dave. Sorry for the lack of information. Yes, 2nd workbook open,
    > > > tried to activate it and select it thus:
    > > > Workbooks("filename.xls").Activate
    > > > Sheets("Sheet1").Select
    > > > and got the exact same error.
    > > > I was trying to refer to the system label for the worksheet rather than the
    > > > user defined string.
    > > > I want to take a string from workbook 1 and search for its match in workbook 2
    > > >
    > > > "Dave Peterson" wrote:
    > > >
    > > > > Is there a workbook named Filename.xls open?
    > > > > If yes, does that workbook have a worksheet named Sheet1?
    > > > >
    > > > > And if yes to both, is that workbook the active workbook--you can only select a
    > > > > worksheet on the active workbook.
    > > > >
    > > > > Workbooks("filename.xls").Select
    > > > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > > > >
    > > > > Or maybe:
    > > > >
    > > > > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("a1"), _
    > > > > scroll:=true
    > > > >
    > > > > would be sufficient.
    > > > >
    > > > > Matilda wrote:
    > > > > >
    > > > > > Help please! Why am I getting a Runtime error 9 subscript out of range error
    > > > > > on this statement?
    > > > > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > > > > >
    > > > > > Many TIAs
    > > > >
    > > > > --
    > > > >
    > > > > Dave Peterson
    > > > >

    > >
    > > --
    > >
    > > Dave Peterson
    > >


    --

    Dave Peterson

  9. #9
    Matilda
    Guest

    Re: out of range error

    Ah... I see where I misled you. The system label I meant was Sheet1 (visible
    in VBA Explorer) as opposed to the label the user sees on the tab. Sheet 1 is
    a variable and not a property, then.

    Thanks Dave, I'll have another go at it. You have the patience of a saint :-)

    "Dave Peterson" wrote:

    > Ok.
    >
    > 'declare a worksheet variable
    > dim wks as worksheet
    >
    > 'start with the other workbook
    > with workbooks("Filename.xls")
    > 'if you want to select, that workbook has to active
    > .activate
    > 'loop through all the worksheets in that filename.xls workbook
    > for each wks in .worksheets
    > 'if the codename--the name you see in the project explorer
    > 'Sheet1(nameuserseesontab)
    > if lcase(wks.codename) = "sheet1" then
    > 'is equal to sheet1, we found it, so select it
    > wks.select
    > 'and get out
    > exit for
    > end if
    > next wks
    > end with
    >
    > But maybe I misunderstood what "system label" meant.
    >
    > Matilda wrote:
    > >
    > > Sorry Dave, I just can't get it to work. Had a few days break to see if a
    > > fresh start helps but no deal. I have a heap of code that does work, I just
    > > can't get it to open the appropriate worksheet to work on!
    > > It should be simple ... can you comment your code for me so I can see why it
    > > isn't doing what I want it to do?
    > >
    > > two workbooks
    > > workbook1 has valueIwantTo Find In Workbook2
    > > workbook2 has n sheets, match will be in one of them
    > > need to open wkbk2 and run search routine (which works fine)
    > >
    > > Sorry to be a pain
    > >
    > > "Dave Peterson" wrote:
    > >
    > > > system label = Codename
    > > > (the name you see in the VBE project explorer?)
    > > >
    > > > dim wks as worksheet
    > > > with workbooks("Filename.xls")
    > > > .activate
    > > > for each wks in .worksheets
    > > > if lcase(wks.codename) = "sheet1" then
    > > > wks.select
    > > > exit for
    > > > end if
    > > > next wks
    > > > end with
    > > >
    > > >
    > > >
    > > > Matilda wrote:
    > > > >
    > > > > Thanks, Dave. Sorry for the lack of information. Yes, 2nd workbook open,
    > > > > tried to activate it and select it thus:
    > > > > Workbooks("filename.xls").Activate
    > > > > Sheets("Sheet1").Select
    > > > > and got the exact same error.
    > > > > I was trying to refer to the system label for the worksheet rather than the
    > > > > user defined string.
    > > > > I want to take a string from workbook 1 and search for its match in workbook 2
    > > > >
    > > > > "Dave Peterson" wrote:
    > > > >
    > > > > > Is there a workbook named Filename.xls open?
    > > > > > If yes, does that workbook have a worksheet named Sheet1?
    > > > > >
    > > > > > And if yes to both, is that workbook the active workbook--you can only select a
    > > > > > worksheet on the active workbook.
    > > > > >
    > > > > > Workbooks("filename.xls").Select
    > > > > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > > > > >
    > > > > > Or maybe:
    > > > > >
    > > > > > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("a1"), _
    > > > > > scroll:=true
    > > > > >
    > > > > > would be sufficient.
    > > > > >
    > > > > > Matilda wrote:
    > > > > > >
    > > > > > > Help please! Why am I getting a Runtime error 9 subscript out of range error
    > > > > > > on this statement?
    > > > > > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > > > > > >
    > > > > > > Many TIAs
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Dave Peterson
    > > > > >
    > > >
    > > > --
    > > >
    > > > Dave Peterson
    > > >

    >
    > --
    >
    > Dave Peterson
    >


  10. #10
    Dave Peterson
    Guest

    Re: out of range error

    That "system label" is called the codename of the worksheet.

    I think the suggested code should get you closer.

    Matilda wrote:
    >
    > Ah... I see where I misled you. The system label I meant was Sheet1 (visible
    > in VBA Explorer) as opposed to the label the user sees on the tab. Sheet 1 is
    > a variable and not a property, then.
    >
    > Thanks Dave, I'll have another go at it. You have the patience of a saint :-)
    >
    > "Dave Peterson" wrote:
    >
    > > Ok.
    > >
    > > 'declare a worksheet variable
    > > dim wks as worksheet
    > >
    > > 'start with the other workbook
    > > with workbooks("Filename.xls")
    > > 'if you want to select, that workbook has to active
    > > .activate
    > > 'loop through all the worksheets in that filename.xls workbook
    > > for each wks in .worksheets
    > > 'if the codename--the name you see in the project explorer
    > > 'Sheet1(nameuserseesontab)
    > > if lcase(wks.codename) = "sheet1" then
    > > 'is equal to sheet1, we found it, so select it
    > > wks.select
    > > 'and get out
    > > exit for
    > > end if
    > > next wks
    > > end with
    > >
    > > But maybe I misunderstood what "system label" meant.
    > >
    > > Matilda wrote:
    > > >
    > > > Sorry Dave, I just can't get it to work. Had a few days break to see if a
    > > > fresh start helps but no deal. I have a heap of code that does work, I just
    > > > can't get it to open the appropriate worksheet to work on!
    > > > It should be simple ... can you comment your code for me so I can see why it
    > > > isn't doing what I want it to do?
    > > >
    > > > two workbooks
    > > > workbook1 has valueIwantTo Find In Workbook2
    > > > workbook2 has n sheets, match will be in one of them
    > > > need to open wkbk2 and run search routine (which works fine)
    > > >
    > > > Sorry to be a pain
    > > >
    > > > "Dave Peterson" wrote:
    > > >
    > > > > system label = Codename
    > > > > (the name you see in the VBE project explorer?)
    > > > >
    > > > > dim wks as worksheet
    > > > > with workbooks("Filename.xls")
    > > > > .activate
    > > > > for each wks in .worksheets
    > > > > if lcase(wks.codename) = "sheet1" then
    > > > > wks.select
    > > > > exit for
    > > > > end if
    > > > > next wks
    > > > > end with
    > > > >
    > > > >
    > > > >
    > > > > Matilda wrote:
    > > > > >
    > > > > > Thanks, Dave. Sorry for the lack of information. Yes, 2nd workbook open,
    > > > > > tried to activate it and select it thus:
    > > > > > Workbooks("filename.xls").Activate
    > > > > > Sheets("Sheet1").Select
    > > > > > and got the exact same error.
    > > > > > I was trying to refer to the system label for the worksheet rather than the
    > > > > > user defined string.
    > > > > > I want to take a string from workbook 1 and search for its match in workbook 2
    > > > > >
    > > > > > "Dave Peterson" wrote:
    > > > > >
    > > > > > > Is there a workbook named Filename.xls open?
    > > > > > > If yes, does that workbook have a worksheet named Sheet1?
    > > > > > >
    > > > > > > And if yes to both, is that workbook the active workbook--you can only select a
    > > > > > > worksheet on the active workbook.
    > > > > > >
    > > > > > > Workbooks("filename.xls").Select
    > > > > > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > > > > > >
    > > > > > > Or maybe:
    > > > > > >
    > > > > > > application.goto Workbooks("filename.xls").Worksheets("Sheet1").Range("a1"), _
    > > > > > > scroll:=true
    > > > > > >
    > > > > > > would be sufficient.
    > > > > > >
    > > > > > > Matilda wrote:
    > > > > > > >
    > > > > > > > Help please! Why am I getting a Runtime error 9 subscript out of range error
    > > > > > > > on this statement?
    > > > > > > > Workbooks("filename.xls").Worksheets("Sheet1").Select
    > > > > > > >
    > > > > > > > Many TIAs
    > > > > > >
    > > > > > > --
    > > > > > >
    > > > > > > Dave Peterson
    > > > > > >
    > > > >
    > > > > --
    > > > >
    > > > > Dave Peterson
    > > > >

    > >
    > > --
    > >
    > > Dave Peterson
    > >


    --

    Dave Peterson

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1