VBA runtime error 9

Peter.Stevens2

Board Regular
Joined
Sep 16, 2008
Messages
56
I'm struggling to explain an error message I'm getting when I step through some code I've written. The code below gets a 'sub script out of range' error and I'm not sure why...


Code:
  For i = 1 To 16
    
                Workbooks(dataArray(16) & ".xls").Worksheets(dataArray(1)).Cells(1, i).Value = titleArray(i)
    
           Next i

All Arrays are defined correctly and each variable contains the correct values but it wont work. It's made all the more frustrating as Its very similar to some other code I use (different array variables) which works fine! Any help or suggestions would be really useful as my limited knowledge of VBA does not stretch this far! Many Thanks!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Can you post the code that includes the declarations of the array variables? Also, do you actually have sheets and/or workbooks named whatever the variables are? When you go to Debug in the VBE and hover your cursor over each variable (ie dataArray(16)) what values are actually shown?
 

Peter.Stevens2

Board Regular
Joined
Sep 16, 2008
Messages
56
Hi Richard, thanks for your quick response, apologies for the sloppy layout, haven't had chance to neaten it up yet.

Code:
Sub sortData()
Dim i As Integer
Dim cellObj As Range
Dim dataArray()
Dim titleArray(1 To 16)
Dim bookFound As Boolean
Dim sheetFound As Boolean
Dim wb As Workbook
Dim ws As Worksheet
Dim sWorkbook As Workbook
Dim sWorksheet As Worksheet
Dim TargetRow As Integer
 
'*define column headings*
    
    For i = 1 To 16
    
        titleArray(i) = ThisWorkbook.Worksheets("Position Raw").Cells(1, i)
    
    Next i
    For Each cellObj In ThisWorkbook.Worksheets("Position Raw").Range("P2:P6170") 'needs sorting
    
    cellObj.Offset(0, 2).Value = "DONE"
    
    bookFound = False
    sheetFound = False
'*Check to see if Books and Sheets are created, if not create them*
 
    ReDim dataArray(1 To 16)
        
        For i = 1 To 16
            dataArray(i) = ThisWorkbook.Sheets("Position Raw").Cells(cellObj.Row, i).Value
        Next i
        
        For Each sWorkbook In Workbooks
        
            If dataArray(16) = "" Then Exit For
            
            If sWorkbook.Name = (CStr(dataArray(16) & ".xls")) Then
                bookFound = True
                Exit For
            End If
            
        Next sWorkbook
        
        If bookFound = False Then
        
            If dataArray(16) = "" Then GoTo NextCllobj
            
            Set wb = Workbooks.Add
            wb.SaveAs ThisWorkbook.Path & "\" & dataArray(16)
        End If
        
        For Each sWorksheet In Workbooks(CStr(dataArray(16) & ".xls")).Worksheets
        
            If sWorksheet.Name = dataArray(11) Then
                sheetFound = True
                Exit For
            End If
            
        Next sWorksheet
        
        If sheetFound = False Then
            Set ws = Workbooks(CStr(dataArray(16) & ".xls")).Worksheets.Add
            ws.Name = dataArray(11)
            '*add column headings to new sheet* *Error Occurs Here*
            For i = 1 To 16
    
                Workbooks(dataArray(16) & ".xls").Worksheets(dataArray(11)).Cells(1, i).Value = titleArray(i)
    
            Next i
            
        End If
        
        TargetRow = Workbooks(CStr(dataArray(16) & ".xls")).Worksheets(dataArray(11)).Range("A65000").End(xlUp).Offset(1, 0).Row
        
        With Workbooks(CStr(dataArray(16) & ".xls")).Worksheets(dataArray(11))
            For i = 1 To 16
            
                .Cells(TargetRow, i).Value = dataArray(i)
            Next i
        End With
NextCllobj:
    Next cellObj

Basically it checks to see if there is an open workbook with a particular variable - (16) - if not it creates it. Then it looks in that workbook to see if there is a sheet named after another variable (11) - if not it creates it. I then want to populate the newly created sheet with column headings. They're all defined and the script will create the files and worksheets but it wont populate any of the cells. Thanks for your help.

Pete
 

Peter.Stevens2

Board Regular
Joined
Sep 16, 2008
Messages
56

ADVERTISEMENT

Yes I am, its an 8 digit numberused to identifyan organisational structure. Do I need to include some non numerical values in there?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
You will need to convert the output of the array to a string before you pass it to the Worksheets() collection as you can refer to a worksheet using its Index or by using its name - when you don't convert the array value to a string, VBA thinks you are passing an Index and presumably you don't have 10,000,000 worksheets in your wb hence the Subcript out of range error!

So:

Code:
Workbooks(dataArray(16) & ".xls").Worksheets(CStr(dataArray(11))).Cells(1, i).Value = titleArray(i)

should work.
 

Peter.Stevens2

Board Regular
Joined
Sep 16, 2008
Messages
56
Schoolboy error!! Thanks for pointing that out, I've done the same thing earlier in the code and remembered to Cstr it!
Thanks, that's been driving me crazy!
 

Forum statistics

Threads
1,136,969
Messages
5,678,890
Members
419,787
Latest member
juanam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top