Dumping variant array to range - VBA excel error 1004

Question

I'm using vba for Excel in order to save data into an array by:

Dim allPosts As Variant
allPosts = Range("A2:J5000")

after that I'm changing data in the allPosts array, and then I want to paste it back by:

Range("A2:J5000").Value = allPosts

I'm getting the error:

run time error 1004 Application-defined or object-defined

and the copy stops at a specific cell, when i change the string at this cell to be shorter the problem is solved.

thanks

Submit whatever you have. I don't know about the others but I have no crystal ball at hand. — ApplePie
the changes I'm performing on allPosts array are complicated, hence the built-in macro recorder didn't help — Presen
well, use recorder to see how paste function works; adapt it to your needs — Andrew
the paste function works, only when it reach the very long string cell it crash. thanks! — Presen
Have you tried just looping over your array and pasting it to destination cells one at the time? http://msdn.microsoft.com/en-us/library/office/aa221353(v=office.11).aspx — Andrew

brettdj brettdj · Accepted Answer · 2012-12-02T00:49:20

You can use a second array to store the cell lengths that are too long, and separately iterate over these

From this Microsoft Support Article excel-2003 can't handle writing back array strings longer than 911 characters excel-2010 worked fine on my testing)

The code below:

Sets up a major variant array that reads in the range
Sets up a second blank variant of equal size to the first array
Tests each part of the array for cell length of more than 911 characters and then either
- manipulates the shorter value in the first array, or,
- removes the value from the first array, and then writes it to the second array
The first array is dumped in a single shot back to the range
The second array is iterated cell by cell to dump back the other strings

code

    Sub KudosRickyPonting()
    Dim allPosts As Variant
    Dim allPosts2 As Variant
    Dim vStrs As Variant
    Dim lngRow As Long
    Dim lngCol As Long
    allPosts = Range("A2:J5000").Value2
    ReDim allPosts2(1 To UBound(allPosts, 1), 1 To UBound(allPosts, 2))

    For lngRow = 1 To UBound(allPosts, 1)
        For lngCol = 1 To UBound(allPosts, 2)
            If Len(allPosts(lngRow, lngCol)) < 912 Then
                allPosts(lngRow, lngCol) = "Updated:" & allPosts(lngRow, lngCol)
            Else
                allPosts2(lngRow, lngCol) = "NEW PART " & allPosts(lngRow, lngCol)
                'erase long value from first array
                allPosts(lngRow, lngCol) = vbNullString
            End If
        Next
    Next
    Range("A2:J5000").Value = allPosts

    For lngRow = 1 To UBound(allPosts2, 1)
        For lngCol = 1 To UBound(allPosts2, 2)
            If Len(allPosts2(lngRow, lngCol)) > 0 Then Range("A2").Offset(lngRow - 1, lngCol - 1).Value2 = allPosts2(lngRow, lngCol)
        Next
    Next
    End Sub

Dumping variant array to range - VBA excel error 1004

1 Answers