Run-time error '1004'. - array size problem

[bobby999]

This code works great except i have 5000 rows in a 2 column list. it works fine for approx 2000 rows, but much more and i get the following error:

Run-time error '1004'.
Application-defined or operation-defined error.

http://support.microsoft.com/kb/818808


Think it is array size problem?
can someone fix this code somehow?

Sub Create_Names()
Dim rList As Range, rCell As Range, oDic As Object, oDic1 As Object
Dim lColumns As Long, lRow As Long, lOffset As Long
Dim lOutColumn As Long, sRoot As String
 
Set rList = Range("A2", Range("A" & Rows.Count).End(xlUp))
lColumns = rList.CurrentRegion.Columns.Count
lOutColumn = rList.Column + lColumns + 2        ' writes the result 2 columns to the right of the table
sRoot = "TEST"                                ' name of the root
 
' store the info
Set oDic = CreateObject("scripting.Dictionary")
oDic.CompareMode = vbTextCompare
For Each rCell In rList
    Set oDic1 = oDic
    For lOffset = 0 To lColumns - 1
        If rCell.Offset(, lOffset).Value = "" Then Exit For
        If Not oDic1.exists(rCell.Offset(, lOffset).Value) Then
            oDic1.Add rCell.Offset(, lOffset).Value, CreateObject("Scripting.Dictionary")
            oDic1(rCell.Offset(, lOffset).Value).CompareMode = vbTextCompare
         End If
        Set oDic1 = oDic1(rCell.Offset(, lOffset).Value)
     Next lOffset
Next rCell
 
' Create the root and the names
Set oDic1 = CreateObject("scripting.dictionary")
oDic1.Add sRoot, oDic
Call CreateNames1(oDic1, lRow, lOutColumn, "")
   
End Sub
 
Sub CreateNames1(ByVal oDic As Object, lRow As Long, lOutColumn As Long, ByVal sPrefix As String)
Dim vkey As Variant, sName As String
 
With oDic
    ' create the names for this level
    For Each vkey In .keys
        If oDic(vkey).Count > 0 Then
            sName = "_" & Replace(Replace(Replace(Replace(Replace(vkey, " ", "_"), "/", "_"), "-", "_"), "&", "_"), "~", "")
            lRow = lRow + 1
            Cells(lRow, lOutColumn) = sPrefix & sName
            Cells(lRow, lOutColumn + 1).Resize(, .Item(vkey).Count) = .Item(vkey).keys
            Names.Add sPrefix & sName, Cells(lRow, lOutColumn + 1).Resize(, .Item(vkey).Count)
        End If
    Next vkey
       
    ' create the names for the sublevels
    For Each vkey In .keys
        If oDic(vkey).Count > 0 Then
            sName = "_" & Replace(Replace(Replace(Replace(Replace(vkey, " ", "_"), "/", "_"), "-", "_"), "&", "_"), "~", "")
            Call CreateNames1(oDic(vkey), lRow, lOutColumn, sPrefix & sName)
        End If
    Next vkey
End With
End Sub

[Torc]

Welcome to the forums,

On which line do you get the error?

[bobby999]

thank you. this line:

Cells(lRow, lOutColumn + 1).Resize(, .Item(vkey).Count) = .Item(vkey).keys

[Torc]

I think that the problem with that line is not an Array Size Problem

According to the help files on my Excel Resize is used to resize a
specified range.

I do not think that it will work with a Cell.

Resize Property
Resizes the specified range. Returns a Range object that represents the resized range.

expression.Resize(RowSize, ColumnSize)
expression Required. An expression that returns a Range object to be resized.

RowSize Optional Variant. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same.

ColumnSize Optional Variant. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same.

[anhn]

Yes, .Resize will work with a cell (in fact, a cell is a range with one row and one column.) However, with aCell.Resize(, ColumnSize), you have to make sure ColumnSize does not exceed the limit of number of columns.

You may have .Item(vkey).Count greater than number of worksheet columns (256 for Excel-2003 or 16384 for Excel-2007.)

Add this line prior to the line that causes error to check that value:

Debug.Print .Item(vkey).Count

[bobby999]

Yes thats it!

Im in excel 2003 and just the first named list created (entitled "Animal" in this code) is too long

Can someone edit the code so that just the first named list is vertical, and then the rest carry on as normal horizontally? ( as also too many names for them to all be vertical)


Thanks!!!

[bobby999]

please help! taking me hours and just mesin the code up!!!

anhn????

[bobby999]

I have the perfect logical solution, just need help to code it!!!!

Once data is added to the arrays ( i think this is done first)
Order arrays by number of elements
If array > 240 elements
Then create named list vertically ( then move to next column)
ELse
create rest or named list horizontally.


Pleas help me edit the code above!!!!
i cant ahve them all vertical as ther will be more than 256 lists, however i only excep 1 or 2 to be more than 256 names...!!!