将变体数组转储到范围 - VBA Excel 错误1004

Question

我正在使用Excel中的vba，将数据保存到数组中：

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

之后我会更改allPosts数组中的数据，然后我想通过以下方式将其粘贴回来：

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

我遇到了以下错误：

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

当我将特定单元格中的字符串更改为较短的字符串时，复制停止在该单元格处，问题得以解决。

谢谢。

- Presen

---

提交您所拥有的内容。我不知道其他人，但我手头没有水晶球。 - ApplePie

我对 allPosts 数组所做的更改很复杂，因此内置的宏记录器没有帮助。 - Presen

好的，使用录制器来查看粘贴功能的工作原理；将其调整到你自己的需要。 - Andrew

粘贴功能正常工作，只有当它到达非常长的字符串单元格时才会崩溃。谢谢！ - Presen

你尝试过循环遍历数组并逐个将其粘贴到目标单元格中吗？http://msdn.microsoft.com/en-us/library/office/aa221353(v=office.11).aspx - Andrew

@Andrew，宏录制器对于基本的手动操作模仿非常有用 - 使用变量数组是一种没有手动等效的编程技术。 - brettdj

Answer 1

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

根据此 Microsoft 支持文章，excel-2003 无法处理字符串长度超过911个字符的数组写回问题。 （在我的测试中，excel-2010 可以正常工作）

以下是代码：

1. 设置一个主要变量数组来读取范围
2. 设置一个大小相同的空白变体数组
3. 测试每个数组部分的单元格长度是否超过911个字符，然后执行以下操作之一：
   • 操纵第一个数组中较短的值，或者
   • 从第一个数组中删除该值，然后将其写入第二个数组
4. 第一个数组一次性转储回范围
5. 迭代第二个数组中的单元格以转储其他字符串

代码

    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

- brettdj

---

谢谢！有没有一种方法可以切分比911更长的字符串？这样，当出现长字符串时，我将对所有帖子进行正常运行，然后将其切分，并最终通过Range("A2:J5000").Value = allPosts将所有帖子粘贴回表格中。 - Presen

同意Doug的观点...非常好 :) - Siddharth Rout

升级以表彰KudosRickyPonting - dilbert