Run-time error '1004'. - array size problem
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?
Code:
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
Re: Run-time error '1004'. - array size problem
Welcome to the forums,
On which line do you get the error?
Re: Run-time error '1004'. - array size problem
thank you. this line:
Cells(lRow, lOutColumn + 1).Resize(, .Item(vkey).Count) = .Item(vkey).keys
Re: Run-time error '1004'. - array size problem
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.
Quote:
Originally Posted by Microsoft Visual Basic Help
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.
Re: Run-time error '1004'. - array size problem
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
Re: Run-time error '1004'. - array size problem
:eek:
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!!! :o
Re: Run-time error '1004'. - array size problem
please help! taking me hours and just mesin the code up!!!
anhn????
Re: Run-time error '1004'. - array size problem
:eek:
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...!!!
:eek: