+ Reply to Thread
Results 1 to 4 of 4

Run-time error '9' ---- Code to fix included.

  1. #1

    Run-time error '9' ---- Code to fix included.

    I have a problem with the following code and I cannot get it to run. I
    am getting a "Run-time error
    '9': Subscript out of range" error when I try to run this code. Here
    is the code:


    Sub openfilesInALocation()
    Dim i As Integer, wb As Workbook
    With Application.FileSearch
    ..NewSearch
    ..LookIn =3D "C:\Documents and Settings\chrisf\My Documents\vbatest"
    ..SearchSubFolders =3D False
    ..Filename =3D "*.xls"
    ..Execute
    For i =3D 1 To .FoundFiles.Count
    'Open each workbook
    Set wb =3D Workbooks.Open(Filename:=3D.Foun=ADdFiles(i))
    'Perform the operation on the open workbook


    -----------> Errors on the following line:


    wb.Worksheets("sheet1").Range(=AD"A1").Select


    'Save and close the workbook
    wb.Save
    wb.Close
    'On to the next workbook
    Next i
    End With
    End Sub


    If anyone can solve this for me I would appreciate the help!


    -Chris


  2. #2
    Rody Meulman
    Guest

    Re: Run-time error '9' ---- Code to fix included.

    Had the same error a long time ago. Split the select argument. First select
    the sheet, and then select the range.

    HTH,

    Rody

    <[email protected]> schreef in bericht
    news:[email protected]...
    I have a problem with the following code and I cannot get it to run. I
    am getting a "Run-time error
    '9': Subscript out of range" error when I try to run this code. Here
    is the code:


    Sub openfilesInALocation()
    Dim i As Integer, wb As Workbook
    With Application.FileSearch
    ..NewSearch
    ..LookIn = "C:\Documents and Settings\chrisf\My Documents\vbatest"
    ..SearchSubFolders = False
    ..Filename = "*.xls"
    ..Execute
    For i = 1 To .FoundFiles.Count
    'Open each workbook
    Set wb = Workbooks.Open(Filename:=.Foun*dFiles(i))
    'Perform the operation on the open workbook


    -----------> Errors on the following line:


    wb.Worksheets("sheet1").Range(*"A1").Select


    'Save and close the workbook
    wb.Save
    wb.Close
    'On to the next workbook
    Next i
    End With
    End Sub


    If anyone can solve this for me I would appreciate the help!


    -Chris



  3. #3

    Re: Run-time error '9' ---- Code to fix included.

    Rody-

    Thanks for the help.....but I am still getting the error. The problem
    lies with how I am naming the sheet. The name of "Sheet1" is "Jun".
    If I change the code to select "Jun" instead of "Sheet1" it runs fine.
    How can I get it reference the first sheet in the file....not a
    specifically named sheet?

    Thanks for the help!
    -Chris


  4. #4
    Chip Pearson
    Guest

    Re: Run-time error '9' ---- Code to fix included.

    You can reference a worksheet by number instead of name. E.g,

    Worksheet(1)


    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com


    <[email protected]> wrote in message
    news:[email protected]...
    > Rody-
    >
    > Thanks for the help.....but I am still getting the error. The
    > problem
    > lies with how I am naming the sheet. The name of "Sheet1" is
    > "Jun".
    > If I change the code to select "Jun" instead of "Sheet1" it
    > runs fine.
    > How can I get it reference the first sheet in the file....not a
    > specifically named sheet?
    >
    > Thanks for the help!
    > -Chris
    >




+ Reply to 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