将变量数组转储到范围 - VBA excel 错误 1004
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13663370/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dumping variant array to range - VBA excel error 1004
提问by Presen
I'm using vbafor Excel in order to save data into an array by:
我正在使用vbafor Excel 以通过以下方式将数据保存到数组中：
Dim allPosts As Variant
allPosts = Range("A2:J5000")
after that I'm changing data in the allPostsarray, and then I want to paste it back by:
之后，我正在更改allPosts数组中的数据，然后我想通过以下方式将其粘贴回：
Range("A2:J5000").Value = allPosts
I'm getting the error:
我收到错误：
run time error 1004 Application-defined or object-defined
运行时错误 1004 应用程序定义或对象定义
and the copy stops at a specific cell, when i change the string at this cell to be shorterthe problem is solved.
并且副本在特定单元格处停止，当我将此单元格处的字符串更改为更短时，问题就解决了。
thanks
谢谢
回答by brettdj
You can use a second array to store the cell lengths that are too long, and separately iterate over these
您可以使用第二个数组来存储太长的单元格长度，并分别迭代这些
From this Microsoft Support Articleexcel-2003can't handle writing back array strings longer than 911characters excel-2010worked fine on my testing)
从这篇 Microsoft 支持文章excel-2003无法处理写回长度超过911 个字符的数组字符串excel-2010在我的测试中运行良好）
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
- 设置读取范围的主要变量数组
- 设置与第一个数组相同大小的第二个空白变体
- 测试数组的每个部分的单元格长度是否超过 911 个字符，然后
- 操作第一个数组中较短的值，或者，
- 从第一个数组中删除值，然后将其写入第二个数组
- 第一个阵列单枪甩回射程
- 第二个数组逐个单元地迭代以转储其他字符串
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
