Application-defined or object-defined error
Results 1 to 9 of 9

Thread: Application-defined or object-defined error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,043
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Application-defined or object-defined error

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

    Code:
    Application-defined or object-defined error
    
    


    my array contains about 15000 rows and 50 columns.

    Code:
    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




  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,121
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Application-defined or object-defined error

    Is there anything in the array that could be (mis) construed as a formula/reference?
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,043
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Application-defined or object-defined error

    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?

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,121
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Application-defined or object-defined error

    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.
    If posting code please use code tags.

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Application-defined or object-defined error

    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)

  6. #6
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,043
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Application-defined or object-defined error

    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.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,878
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Application-defined or object-defined error

    Can you post the entire code?
    I see no reason why you would get the error mentioned on that line of code.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,121
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Application-defined or object-defined error

    Have you checked the contents of the original array?
    If posting code please use code tags.

  9. #9
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,043
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Application-defined or object-defined error

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

    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:

    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
    
    








    Thanks for all the suggestions.

    Last edited by tiredofit; Aug 16th, 2019 at 09:08 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •