Reset array variant to range - VBA excel error 1004

Question

I use vba for Excel to save data in an array:

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

after that I change the data in the allPosts array, and then I want to insert it back:

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

I get an error message:

1004 runtime error Defined by application or object

and the copy stops in a specific cell when I change the row in that cell to be shorter . The problem is resolved.

thanks

Answer 1

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

From this Microsoft support article excel-2003 cannot handle array strings exceeding 911 characters excel-2010 worked fine in my testing)

Code below:

• Sets an array of basic options that reads in a range
• Sets the second empty equal size option for the first array
• Checks each part of the array for a cell length of more than 911 characters, and then either
  • manipulates a shorter value in the first array or
  • removes a value from the first array and then writes it to the second array
• The first array is dropped back into the range with one shot.
• The second array is an iterated cell by cell to flush the rest of the rows.

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