+ Reply to Thread
Results 1 to 8 of 8

Run-time error '9': subscript out of range - error occurs on multiple computers except one

  1. #1
    Registered User
    Join Date
    08-21-2012
    Location
    Perth
    MS-Off Ver
    Excel 2007
    Posts
    8

    Run-time error '9': subscript out of range - error occurs on multiple computers except one

    I have looked at various posts but haven't found one that relates (i think).

    I get the error message:
    Run-time error '9': subscript out of range

    Which the debugger takes me to this particular line in the VBA:
    Windows("ISO - 10t qc & fs Calibration Spreadsheet").Activate

    The file it is trying to import is then opened as a separate worksheet.

    The VBA is supposed to import data from 6 x txt files onto separate sheets.

    This works on one computer (using excel 2003), but not on other computers using excel 2003 or 2007.

    The file name, as far as I can tell is correct.

    The rest of the code:
    Public ImportedBook As String 'filetype i.e. ImportedBook.cpd

    Sub Main()

    OpenCalibrationfile
    CopyPaste "qc 1"

    OpenCalibrationfile
    CopyPaste "qc 2"

    OpenCalibrationfile
    CopyPaste "qc 3"

    OpenCalibrationfile
    CopyPaste "fs 1"

    OpenCalibrationfile
    CopyPaste "fs 2"

    OpenCalibrationfile
    CopyPaste "fs 3"

    End Sub

    Sub OpenCalibrationfile()
    Importfile = Application _
    .GetOpenFilename("All Files (*.*),*.*", , "Open calibration text file", "Select File")
    If Importfile = False Then
    MsgBox "Invalid file selection - job cancelled"
    End
    End If
    Application.ScreenUpdating = False
    Workbooks.OpenText Filename:=Importfile, Origin:=xlWindows, _
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=False, FieldInfo:=Array(1, 1)
    ImportedBook = ActiveWorkbook.Name
    End Sub


    Sub CopyPaste(SheetName)
    Windows(ImportedBook).Activate
    Cells.Select
    Selection.Copy
    Windows("ISO - 10t qc & fs Calibration Spreadsheet").Activate
    Sheets(SheetName).Select
    Cells(1, 1).Select
    ActiveSheet.Paste
    Windows(ImportedBook).Activate
    Application.CutCopyMode = False
    Windows("ISO - 10t qc & fs Calibration Spreadsheet").Activate
    Workbooks(ImportedBook).Close (False)
    'closes the import file without saving it to the hard disc
    End Sub


    Really appreciated any assistance on this. I didn't write the code, I am just trying to get it to work on other computers.

    Cheers

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Run-time error '9': subscript out of range - error occurs on multiple computers except

    Is the workbook 'ISO - 10t qc & fs Calibration Spreadsheet' opened in the code or is it the workbook with the code in it?

    Have you tried adding the extension, eg xlsm, xlsx?

    PS Can you add code tags?
    If posting code please use code tags, see here.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Run-time error '9': subscript out of range - error occurs on multiple computers except

    My guess is the window name is not exactly "ISO - 10t qc & fs Calibration Spreadsheet" on the other computers.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    08-21-2012
    Location
    Perth
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Run-time error '9': subscript out of range - error occurs on multiple computers except

    Hi Norie,

    I changed the line to: 'ISO - 10t qc & fs Calibration Spreadsheet.xls' and it worked.

    Awesome, thank you very much!

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Run-time error '9': subscript out of range - error occurs on multiple computers except

    Glad that worked.

    By the way, there are ways you could refer to the workbook without actually having to use the name.

  6. #6
    Registered User
    Join Date
    08-21-2012
    Location
    Perth
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Run-time error '9': subscript out of range - error occurs on multiple computers except

    Keen to learn...
    Could you suggest one of the ways.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Run-time error '9': subscript out of range - error occurs on multiple computers except

    If 'ISO - 10t qc & fs Calibration Spreadsheet.xls' is the workbook with the code in it you can simply use ThisWorkbook when you want to refer to it.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-21-2012
    Location
    Perth
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Run-time error '9': subscript out of range - error occurs on multiple computers except

    I will give it a try.

    Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Run-time Error '9': Subscript out of Range
    By Veena Anoohya in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-19-2014, 05:06 AM
  2. [SOLVED] Run-time error '9': Subscript out of range
    By billj in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-03-2013, 11:30 PM
  3. Run-time Error '9': Subscript out of Range
    By bcn1988 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-11-2012, 03:01 PM
  4. Run-time Error '9': Subscript out of range
    By tryaz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-23-2009, 02:13 PM
  5. Run time error-subscript out of range
    By ldd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-21-2006, 01:37 PM

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