将变量数组转储到范围 - VBA excel错误1004 [英] Dumping variant array to range - VBA excel error 1004

问题描述

我正在使用 vba 的问题通过以下方式将数据保存到数组中：

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

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

之后，我正在更改 allPosts 数组，然后我想将其粘贴回来：

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

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

我收到错误：

运行时错误1004应用程序定义或对象定义

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.

谢谢

推荐答案

您可以使用第二个数组来存储太长的单元格长度，并单独迭代这些

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

从此Microsoft支持文章 excel-2003 不能处理写回数组字符串 911 字符 excel- 2010年在我的测试中工作正常）

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)

以下代码：

1. 设置一个主要变体数组，读取范围


2. 设置与第一个数组相等大小的第二个空白变量


3. 数组的一部分长度超过了911个字符，然后
   
   
   
   • 操纵第一个数组中较短的值，或者
   
   
   • 删除第一个数组中的值，然后将其写入第二个数组
   
   

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

代码

    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

这篇关于将变量数组转储到范围 - VBA excel错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！