Application-defined or object-defined error

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,102
I am trying to return the contents of a fairly large array but get this error message:

Rich (BB code):
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Application-defined or object-defined error
my array contains about 15000 rows and 50 columns.

Rich (BB code):
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sheet1.Cells(1, 1).Resize(15000, 50).Value = MyArray
If I change the 50 to say 30, it returns the results.

What is the limit re returning arrays?

I am using Excel 365, 64 bit.

Thanks


[/FONT]<strike>
</strike>
[/FONT]
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
Is there anything in the array that could be (mis) construed as a formula/reference?
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,102
How would I find that out? Things like "[" and "]"?

What I have experimented with is to replace the contents of every cell with a single character, such as the letter a.

The dimensions of the array is still 15000 by 50 and it had no problems, which led me to think perhpas it has something to do with the number of characters in some of the cells.

I investigated and some cells contained "mini-essays" or a few hundred characters.

Could that be the problem?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
There is a limit to how much data you can transfer from an array to a sheet but if you go over that limit you wouldn't get the error you describe, you would end up with a bunch of #N/As on the worksheet.

The only time I've come across the error you describe is when there's been something in the array that, for whatever reason, Excel thinks is a formula, don't know about no of characters though.
 

Trixterz

New Member
Joined
Aug 15, 2019
Messages
11
There is one way to test it, try using this code on it...

Code:
Dim MyArray(1 To 15000, 1 To 50) As String
Dim Rows As Integer, Cols As Integer


For Rows = 1 To 15000
    For Cols = 1 To 50
        MyArray(Rows, Cols) = Cells(Rows, Cols).Value
    Next Cols
Next Rows


MsgBox "Test results: " & MyArray(4, 2)
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,102
Thanks for the suggestions.

I've created a mega array of over 1m rows and a few hundred cols (every cell contains just the number 1) and again it's fine.

So my conclusion is that there has to be something with contents of the original 15000 x 50 array that is causing the problem.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,454
Office Version
365
Platform
Windows
Can you post the entire code?
I see no reason why you would get the error mentioned on that line of code.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
Have you checked the contents of the original array?
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,102
I've did some digging around and someone else also had the same problem:

Rich (BB code):
https://stackoverflow.com/questions/34930121/excel-2013-vba-writing-array-to-sheet-application-defined-or-object-defined-er



so I formatted all the data as General first, then ran it and it was fine.

FYI, here is my code:

Rich (BB code):
Dim DataArray As Variant
    DataArray = Sheet3.Cells(1, 1).CurrentRegion.Value
    Dim Counter As Long
    For Counter = 2 To FnLastRow.LRow(wks:=Sheet3)
        If DataArray(Counter - 6, 13) = DataArray(Counter - 6, 17) Then
            DataArray(Counter - 6, 14) = DataArray(Counter - 6, 14) + DataArray(Counter - 6, 18)
            DataArray(Counter - 6, 18) = "DELETE"
        End If
    Next Counter
    With Sheet3
        .Cells.ClearContents
        .Cells(1, 1).Resize(FnLastRow.LRow(wksSheet3) - 6, 50).Value = DataArray
    End With





<strike>
</strike>



Thanks for all the suggestions.

 
Last edited:

Forum statistics

Threads
1,084,915
Messages
5,380,555
Members
401,688
Latest member
Empa

Some videos you may like

This Week's Hot Topics

Top