Application-defined or object-defined error

[tiredofit]

I am trying to return the contents of a fairly large array but get this error message:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Application-defined or object-defined error

my array contains about 15000 rows and 50 columns.

[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]

Is there anything in the array that could be (mis) construed as a formula/reference?

 

[tiredofit]

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]

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]

There is one way to test it, try using this code on it...

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]

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]

Can you post the entire code?
I see no reason why you would get the error mentioned on that line of code.

 

[Norie]

Have you checked the contents of the original array?

 

[tiredofit]

I've did some digging around and someone else also had the same problem:

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:

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.