Dumping variant array to range – VBA excel error 1004

Accepted answer +12 upvotes

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:

1. Sets up a major variant array that reads in the range
2. Sets up a second blank variant of equal size to the first array
3. 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
4. The first array is dumped in a single shot back to the range
5. 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