Announcement

Collapse
No announcement yet.

Run-time error 9: Subscript out of range (Possible Array Error)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Run-time error 9: Subscript out of range (Possible Array Error)



    Hi everyone,
    I am working on creating a automate Timesheet. The timesheet will have information about the employee's id, department, first name and last name. In the timesheet it will also includes the first date until the fourteenth date of the bi-weekly payroll. The date will be created automatically when msgbox pop-up asking which week I want to work on (see below: "iInput code").
    This code below involves 2 excel files. The first file (MasterfileAuditReport.xls)contains employees' name, id, department and their status (active, terminated, etc). The second file (Timesheet.xls) will be created from VBA code in the first file.
    But when I tried to run the following VBA script on the first file, I ran into the following error message "Run-time error '9': Subscript out of range" on this line (with '****' sign).
    I have been working on this VBA for few weeks but I couldn't find out how to fix this problem.
    I hope some of you can give me some suggestion and help.
    I really appriciate this, thank you in advance.
    Have a nice day.


    Code:
    Sub CreateTimesheet()
    'Variable for Workbook
        Dim sDepartment As String
        Dim sID As String
        Dim sName As String
    
    'Variables for MasterFile
        Dim iColumn As Integer
        Dim iRow As Integer
        Dim sCellValue As String
        Dim sCellValue1 As String
        Dim sCellValue2 As String
        Dim sCellValue3 As String
    'Varibles for Calendar
        Dim iInput As Integer
        Dim dInitialDate As Date
        Dim dDateOfWeek As Date
        Dim iLoopValue As Integer
        Dim NewArray(1 To 15) As Date
        Dim LoopArray As Integer
        Dim iRow1 As Integer
        Dim iRow2 As Integer
    
        iRow1 = 9
        iRow2 = 18
    
    'Open the Timesheet workbook
    'Workbooks.Open Filename:="Timesheet.xls"
    
    'Code to clean up Master file starts here
    sCellValue1 = "Terminated"
    sCellValue2 = "On Leave of Absence"
    sCellValue3 = "Deceased"
    
    iColumn = 12
    iRow = 12
    
    iInput = InputBox("Which Payroll Period do you want to generate?")
    dInitialDate = "10/31/2004"
    iLoopValue = 0
    dDateOfWeek = dInitialDate + (iInput * 14)
    LoopArray = iLoopValue + 1
    dDateOfWeek = dDateOfWeek - 1
    
    'Loop Starts
    'Do Until IsEmpty(Workbooks("Masterfile.xls").Worksheets(1).Cells(iRow, 12))
        'sCellValue = Workbooks("masterfile.xls").Worksheets(1).Cells(iRow, 12).Value
        'If sCellValue = sCellValue1 Or sCellValue = sCellValue2 Or sCellValue = sCellValue3 Then
            'ActiveSheet.Cells(iRow, 12).Select
            'Selection.EntireRow.Delete
        'Else
            'iRow = iRow + 1
        'End If
    'Loop
    
    'Code to get info for Department, ID and Name from Mastefile.xls
            Do Until IsEmpty(Workbooks("Masterfile.xls").Worksheets(1).Cells(iRow, 1))
    
            'Code to create calendar starts here
    
    
                'Loop for the first week
                Do While iLoopValue < 7
                    dDateOfWeek = dDateOfWeek + 1
                    NewArray(LoopArray) = dDateOfWeek
                    Cells((LoopArray + 8), 2).Clear
                    Cells((LoopArray + 8), 2) = NewArray(LoopArray)
                    LoopArray = LoopArray + 1
                    iLoopValue = iLoopValue + 1
                Loop
    
                'Loop for the second week
                Do While iLoopValue < 14
                    dDateOfWeek = dDateOfWeek + 1
                    NewArray(LoopArray) = dDateOfWeek
                    Cells((LoopArray + 10), 2).Clear
                    Cells((LoopArray + 10), 2) = NewArray(LoopArray)
                    LoopArray = LoopArray + 1
                    iLoopValue = iLoopValue + 1
                Loop
    
            'Beginning Date
                Cells(2, 9).Clear
                Cells(2, 9) = NewArray(1)
                Cells(2, 11).Clear
                Cells(2, 11) = NewArray(14)
    
                'Deparment
                Workbooks("Masterfile.xls").Worksheets(1).Cells(iRow, 1) = sDepartment
                Workbooks("Masterfile.xls").Worksheets(1).Cells(iRow, 1).Copy
                Workbooks("Timesheet.xls").Worksheets(1).Cells(3, 2).PasteSpecial
    
                'ID
                Workbooks("Masterfile.xls").Worksheets(1).Cells(iRow, 2) = sID
                Workbooks("Masterfile.xls").Worksheets(1).Cells(iRow, 2).Copy
                Workbooks("Timesheet.xls").Worksheets(1).Cells(2, 6).PasteSpecial
    
                'Name
                Workbooks("Masterfile.xls").Worksheets(1).Cells(iRow, 3) = sName
                Workbooks("Masterfile.xls").Worksheets(1).Cells(iRow, 3).Copy
                Workbooks("Timesheet.xls").Worksheets(1).Cells(2, 2).PasteSpecial
    
                ThisWorkbook.SaveAs Filename:=sName & ".xls", FileFormat:=xlWorkbookNormal
                sDepartment = " "
                sID = " "
                sName = " "
                iRow = iRow + 1
    
            Loop
    
    End Sub

  • #2
    Re: Run-time error 9: Subscript out of range (Possible Array Error)

    Hi,

    Pretty easy to find when you're on the outside (so to speak). <g> Sometimes it's difficult to spot a problem if you're immersed in it!

    Masterfile.xls is out of range (error 9) because it's not open or referenced. Easy way to fix the problem is to simply open it!

    ADD:

    Code:
                 Workbooks.Open Filename:="Masterfile.xls"
    before:

    Code:
        'Open the Timesheet workbook
                Workbooks.Open Filename:="Timesheet.xls"
    that way you'll be seeing Timesheet.xls as the current workbook.


    And inside the LOOPs' 'IF' procedure, change:

    from
    Code:
    							
             Then						
    	ActiveSheet.Cells(iRow, 12).Select
    to:
    Code:
             Then
    	Workbooks("Masterfile.xls").Worksheets(1).Cells(iRow, 12).Select
    or Activate Masterfile.xls, and leave the 'Activesheet' line in...

    Cheers,


    dr
    Last edited by rbrhodes; December 30th, 2004, 10:53. Reason: just learned about code tags
    Cheers,

    dr

    "Questions, help and advice for free, small projects by donation. large projects by quote"

    http://www.ExcelVBA.joellerabu.com

    Comment


    • #3
      Re: Run-time error 9: Subscript out of range (Possible Array Error)

      Can you guys PLEASE READ the post about thread submission, specifically about using the code tags when you post VBA Code. It makes the code far easier to read.

      Can you also edit your posts to add the code tags.

      Thanks
      Kind Regards, Will Riley

      LinkedIn: Will Riley

      Comment


      • #4
        Re: Run-time error 9: Subscript out of range (Possible Array Error)

        Hi slean,

        Welcome to the ozgrid forum.

        Firstly, whether or not the code

        Code:
        Do Until IsEmpty(Workbooks("Masterfile.xls").Worksheets(1).Cells(iRow, 12))
        will work (even if the workbook is open) will depend on whether your Windows setup is configured to display all file extensions. If not, you will need to refer to the workbook without the .xls extension.

        Secondly, as you will experience from your program, using ActiveSheet (and other similar current workbook, sheet and cell selections) can be tricky especially if you have many different workbooks open with many sheets. In your loop you are trying to work down column L of Sheet1 of Masterfile, but refer to ActiveSheet. As Timesheet was the last workbook opened, and your program has no specific statements to affect which workbook or worksheet is active, ActiveSheet will refer to whichever sheet of Timesheet was active when that workbook was opened.

        It would be far better, and more efficient, to define a range variable that refers to the worksheet range you want to use, e.g.:

        Code:
        Dim rngMasterfile As Range, rngTimesheet As Range
        
        ' Change the range references to suit
        Set rngMasterFile = Workbooks("Masterfile.xls").Worksheets(1).Range("L1:L500")
        Workbooks.Open "Timesheet.xls"
        Set rngTimesheet = Workbooks("Timesheet.xls").Worksheets(1).Range("A1:C500")
        You can then refer to the ranges in the format

        Code:
        rngMasterfile(iRow, 1).EntireRow.Delete
        Note that if you Set a range variable to refer to specific rows and/or columns, the range variable's row and column numbering starts at the first cell declared, hence

        Code:
        Set rngMasterfile = Workbooks("Masterfile.xls").Worksheets(1).Range("L1:L500")
        would require a reference of rngMasterfile(3,1) [i.e. row 3, column 1 of the declared range] to refer to cell L3.

        Hope this helps.

        Regards,
        Batman.
        Regards,
        Batman.

        Comment


        • #5
          Re: Run-time error 9: Subscript out of range (Possible Array Error)

          Hi rbrhodes and batman,
          Thank you for your reply and assistant.
          I am looking at your replies right now, and I noticed that both of you recommended me to open the Masterfile.xls and make it active. I will give it a try when I get back home tonight.
          And I will post the result.
          Thanks again,
          -Slean

          Comment


          • #6
            Re: Run-time error 9: Subscript out of range (Possible Array Error)

            Hi slean,

            It will be difficult to do anything with Masterfile if it's not open. I must admit I had assumed either that Masterfile would be open or that it was the workbook holding the macro.

            Opening a workbook will automatically make it the active workbook. However, I would recommend NOT using objects such as "ActiveWorkbook", "ActiveSheet", etc unless there is a specific reason to do so. You can nearly always carry out actions on a workbook, worksheet or cell without activating or selecting the object first.

            Hope this helps.

            Regards,
            Batman.
            Regards,
            Batman.

            Comment


            • #7
              Re: Run-time error 9: Subscript out of range (Possible Array Error)

              Hi again Batman,
              Actually the macro itself is in Masterfile.xls.
              From the tutorial book that I read, as long as if the macro is in the same .xls file, then that .xls file is active, is this true?
              If it is true, do I still need to write open and active code for Masterfile.xls?
              Thanks again,
              -Slean

              Comment


              • #8
                Re: Run-time error 9: Subscript out of range (Possible Array Error)

                Hi again!

                Actually the macro itself is in Masterfile.xls.
                I ran the macro from 'Timesheet' and I got 'Subscript out of range error 9' which told me Masterfile wasn't open!

                From the tutorial book that I read, as long as if the macro is in the same .xls file, then that .xls file is active, is this true?
                Active, perhaps, but not necessarily the "Activesheet". The "Activesheet" is the one displaying...

                If it is true, do I still need to write open and active code for Masterfile.xls?
                Nope, no 'open' code needed.

                So. Masterfile is open and the macro is in Masterfile. The sub opens Timesheet which becomes the "Activesheet".

                I ran your loop this way and didn't get any Error code. It checks the Masterfile reference and deletes the rows in Timesheet! (the "ActiveSheet").

                Beauty.

                Then I look at your original post again...

                The first file (MasterfileAuditReport.xls) contains employees' name, id, department and their status (active, terminated, etc).

                MasterfileAuditReport.xls? Not Masterfile.xls!


                Finally,

                1) you need to change the filename! (twice)

                2) you need to fix the "Activesheet" reference! For now, insert this

                Code:
                Workbooks("MasterfileAuditReport.xls").Activate
                instead of "Activesheet..Cells(iRow, 12).Select

                and you're back in business. Then you can start working on the suggestions for creating range references...<g>

                Cheers,

                dr
                Cheers,

                dr

                "Questions, help and advice for free, small projects by donation. large projects by quote"

                http://www.ExcelVBA.joellerabu.com

                Comment


                • #9
                  Re: Run-time error 9: Subscript out of range (Possible Array Error)

                  hi rbrhodes,
                  Thanks again for your reply,
                  i will give it a try.
                  Thanks,
                  slean

                  Comment


                  • #10


                    Re: Run-time error 9: Subscript out of range (Possible Array Error)

                    Hi slean,

                    When you run a macro, the workbook containing that macro is not necessarily the active workbook. Take the example of running a macro from the Personal workbook, which is most likely hidden; that will not be the active workbook.

                    To refer to the workbook holding the macro you can use ThisWorkbook as in

                    Code:
                    ThisWorkbook.Worksheets(1).Range("L1:L500")
                    I would still advise against using ActiveWorkbook, ActiveSheet, etc. whenever possible as these can change throughout your program as you open, close, activate workbooks, worksheets, etc. so you find yourself having to code references to one specific book or sheet in different ways throughout the program. You will probably get the program to work eventually, but when you come to look at the program again in a few months time you will almost certainly find it difficult to follow to make changes.

                    If you look at my earlier post and look into using the Set function to define a range variable name to identify an area of data, that reference will not need to change throughout the program and will make it much easier to understand.

                    Not only that, but if you constantly refer to, say

                    Code:
                    Workbooks("Book1").Worksheets("Sheet1").Range("A1:A50")
                    VBA has to go through 3 processes each time it refers to that code (possibly in a loop of several hundred or thousand instances) to identify the range affected. Using a range variable refers directly to the range, hence will make your program run more efficiently.

                    Hope this helps.

                    Regards,
                    Batman.

                    PS: No, if Masterfile holds the macro you don't need to write separate code to open Masterfile. Refer back to my comment about file extensions - do you have file extensions displayed in the title bar of Excel? If not, try removing the .xls from your reference to the workbook in the code.
                    Last edited by Batman; December 30th, 2004, 18:27. Reason: Additional info re workbook holding code
                    Regards,
                    Batman.

                    Comment

                    Working...
                    X