VBA runtime error 9

[Peter.Stevens2]

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...

  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!

 

[Richard Schollar]

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]

Hi Richard, thanks for your quick response, apologies for the sloppy layout, haven't had chance to neaten it up yet.

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

 

[Richard Schollar]

Are you naming your worksheets using numbers by any chance?

 

[Peter.Stevens2]

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]

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:

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

should work.

 

[Peter.Stevens2]

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!