+ Reply to Thread
Results 1 to 9 of 9

Runtime error 9 "subscript out of range"

  1. #1
    Registered User
    Join Date
    05-07-2012
    Location
    Rehoboth Beach, Delaware
    MS-Off Ver
    Excel 2007
    Posts
    5

    Runtime error 9 "subscript out of range"

    Hello everyone!

    Problem:– runtime error 9, “subscript out of range” - everywhere!

    Given: Workbooks MEgdF.xls and MEgdB.xls.
    Task: Transfer input data and estimated data from MEgdF.xls to MEgdB.xls.

    Workbook MEgdF.xls
    Worksheet “Data&Parms”, Column H: Input data in cells 1 to 21
    Worksheet “Output”, Column F: estimated data in cells 1 to 41.

    Workbook MEgdB.xls
    Worksheet “Data&Parms”, Column H: Estimated data from MegdF.xls read into cells 1 to 41
    Worksheet “Output”, Column F: Input data from MegdF.xls read into cells 1 to 41.

    I am using Excel 2007 and I know nothing of Excel VBA whatsoever.

    Macros tried available upon request.

    Can anyone help?

    Best regards,

    “MyCousinVinnie

  2. #2
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,008

    Re: runtime error 9 "subscript out of range"

    That error translates into: "some object you tried to access by name cannot be found".

    So, your code tried to access a workbook in a folder it couldn't find. Or a sheet in a workbook it couldn't find, or something like that. Spelling always counts when addressing commands to objects.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    05-07-2012
    Location
    Rehoboth Beach, Delaware
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: runtime error 9 "subscript out of range"

    So where is the error??

    Dim wb As Workbook
    Dim wbMEgdF As Workbook, wbMEgdB As Workbook ' don't be concerned about these names.
    Dim rH As Range, rF As Range
    Dim sFileName As String
    Dim LenH As Long, LenF As Long
    Application.ScreenUpdating = False ' turn off the screen updating
    'Show the open dialog and pass the selected _
    file name To the String variable "sFileName"
    sFileName = Application.GetOpenFilename
    'They have cancelled.
    If sFileName = "False" Then Exit Sub
    Set wb = Workbooks.Open(sFileName)
    If InStr(1, ThisWorkbook.Name, "megdf", vbTextCompare) Then
    Set wbMEgdF = ThisWorkbook
    Set wbMEgdB = wb
    Else
    Set wbMEgdF = wb
    Set wbMEgdB = ThisWorkbook
    MsgBox "If here in the code, the code is not in the expected workbook, quitting"
    Exit Sub
    End If
    LenH = wbMEgdF.Worksheets("Data&Parms").Range("B1").Value ' column H of Data&Parms of wbMEgdF
    LenF = wbMEgdF.Worksheets("Data&Parms").Range("B3").Value ' column F of Output of wbMEgdF
    If LenH = 0 Then
    MsgBox "No entry in MEgdF!Data&Parms.B1 length of data in Data&Parms"
    Exit Sub
    End If
    If LenF = 0 Then
    MsgBox "No entry in MEgdF!Data&Parms.B3 - length of data in output"
    Exit Sub
    End If
    Set rH = wbMEgdF.Worksheets("Data&Parms").Range("H1").Resize(LenH, 1)
    Set rF = wbMEgdF.Worksheets("Output").Range("F1").Resize(LenF, 1)
    rF.Copy
    wbMEgdB.Worksheets("Data&Parms").Range("H1").PasteSpecial xlValues
    rH.Copy
    wbMEgdB.Worksheets("Output").Range("G1").PasteSpecial xlValues
    wbMEgdB.Save ' making a guess you want to save wbMEgdB.xls
    wbMEgdB.Close
    Set wb = Nothing ' free memory
    Set wbMEgdB = Nothing
    Application.ScreenUpdating = True ' turn on the screen updating
    MsgBox "data transfer complete"
    End Sub

  4. #4
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,008

    Re: Runtime error 9 "subscript out of range"

    You're kidding, right?

    When you get the error, click DEBUG. That will highlight the line of code where the failure is occuring. Now you just have to quadruple check that line of code and all the strings mentioned in that line of code.

  5. #5
    Registered User
    Join Date
    05-07-2012
    Location
    Rehoboth Beach, Delaware
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Runtime error 9 "subscript out of range"

    Not at all! I've gone this route.

    The reason that I posted this thread is that I know nothing of EXCEL VBA and automation is essential. It beats doing it by hand. The MEgdF.xls code was produced by a local expert sometime in the nineties and he has long since departed.

    This Macro transfer code was presented recently by another individual knowledgeable in EXCEL VBA, but it does not work either. Debug shows that one error is at, LenH = wbMEgdF.Worksheets("Data&Parms").Range("B1").Value ' column H of Data&Parms of wbMEgdF, and I suspect that Len F is next.

    I am not wedded to any one Macro. All I need is one that solves this problem as stated in my first post.

    Now I can make the above Macro work if I create two "workbooks" named "MEgdF.xls" and "MEgdB.xls" (just spreadsheets) and create worksheets "Data&Parms" and "Output" on each with sheets 2,3 and 4 in between, and with 21 numbers in column H and 41 numbers in column F in "Data&Parms" and "Output" respectively in MEgdF.xls, and then execute. This works perfectly. But when this is tried in the full MEgdF.xls and MEgdB.xls workbooks - NO CIGAR!

  6. #6
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,008

    Re: Runtime error 9 "subscript out of range"

    LenH was declared as a LONG variable, so it can only be a whole number greater than zero.

    So the error on that line of code could mean:

    1. wbMEgdF no longer is defined. When you debug, hover your mouse over this and see if it pops up a value for wbMEgdF

    2. Worksheets("Data&Parms") can't be found in the wb that IS currently defined in wbMEgdF. If #1 is ok, check that current wb for a sheet with that exact name (no hidden spaces at the end).

    3. The value in Range("B1") cannot legitimately fill the LenH variable.

    You might try one of Excel's built in string functions that might successfully convert a "string" value into a long value...

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 05-08-2012 at 05:59 PM.

  7. #7
    Registered User
    Join Date
    05-07-2012
    Location
    Rehoboth Beach, Delaware
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Runtime error 9 "subscript out of range"

    JB as you suggested:

    I hope that I did this correctly.

    I hovered the mouse over LenH and LenF and both read = 0.
    Then both rH and rF = Nothing.

    I also hope that I am reading your statement 2. correctly but within the program
    I find "Data&Parms" reading everywhere as "Data & Parms". Is this what you are referring to?
    If so, should the transfer code be brought into agreement with wbMEgdF.xls and wbMEgdB.xls?

    In addition, when I open the VB Editor, I see (Data & Parms) lisrted as Sheet 1. That's OK.
    But (Output) is listed as sheet 2 and not as Sheet5 as shown on the spreadsheets. A problem?

    Item 3, I am not certain about.

    I will try your suggestions and if still no-go, I will try your select code.

    Thanks for your prompt and kind response!

    Best regards,

    MCV

  8. #8
    Registered User
    Join Date
    05-07-2012
    Location
    Rehoboth Beach, Delaware
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Runtime error 9 "subscript out of range"

    Hey JB you did it!!

    I corrected the code to read "Data & Parms" as in the programs and it worked great.

    Many-many thanks!!

    Yes you are a rocket scientist. I met Robert Goddard's sister and nephew.
    I worked with the latter and he was a very good engineer.
    I also met Werner vB but he could not have helped me here.

    How do I send my cudos for all to note??

    So thanks immensely!

    MyCousinVinnie and Co.

  9. #9
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,008

    Re: Runtime error 9 "subscript out of range"

    Glad you found the discrepancy. VBA is unforgiving of object names, and spaces are a pain in the patootie, eh? Good job.

+ 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