vba Excel 将数组值放入范围内 - 转置会导致类型不匹配错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17728872/
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
Excel Put Array Value in Range - Transpose Gives Type Mismatch Error
提问by Jeffrey Kramer
UPDATE:
更新：
I had two problems that contributed to this error. First, I had a clash with a SAS add-in that was messing up the copy/paste functionality. Second, I had a null value in the array that was causing the transposition to fail. I have handled the null and everything works fine.
我有两个问题导致了这个错误。首先，我与 SAS 插件发生了冲突，该插件破坏了复制/粘贴功能。其次，我在数组中有一个空值导致转置失败。我已经处理了 null 并且一切正常。
I'm trying to assign an array of values to my Excel worksheet. When I use the TRANSPOSE function I am getting type mismatch errors. I don't understand why, there are only about 200 "rows" in the set:
我正在尝试将一组值分配给我的 Excel 工作表。当我使用 TRANSPOSE 函数时，我遇到了类型不匹配错误。我不明白为什么，集合中只有大约 200 个“行”：
Worksheets("xyz").Range("A2").Resize(ValidCode, 5).Value = & _
Application.WorksheetFunction.Transpose(CodeData)
It works fine when I put a static value instead of the transposed array. I don't understand what's happening to it. It is type "Variante/Variante(0 to 4, 0 to 205)"
当我放置一个静态值而不是转置数组时，它工作正常。我不明白这是怎么回事。它是类型“Variante/Variante(0 to 4, 0 to 205)”
The array needs to be transposed because I had to redim preserve it in a code loop (so the "columns" are rows, etc.) I guess I could manually transpose it, but that seems unnecessary.
数组需要转置，因为我必须将其重新存储在代码循环中（因此“列”是行等）我想我可以手动转置它，但这似乎没有必要。
采纳答案by David Zemens
The & character is not necessary and in fact raises a 1004error when I try to implement your code.
& 字符不是必需的，实际上1004当我尝试实现您的代码时会引发错误。
Otherwise, I cannot replicate the error you describe, this example code works with or without Transposefunction, although you need to use Transposeto achieve the desired results.
否则，我无法复制您描述的错误，此示例代码可以使用或不使用Transpose函数，尽管您需要使用Transpose来实现所需的结果。
Sub Test()
Dim ws As Worksheet: Set ws = Sheets("xyz")
Dim rng As Range: Set rng = ws.Range("A2")
Dim r As Integer
Dim c As Integer
Dim CodeData() As Variant
ReDim Preserve CodeData(4, 205) '## Create a dummy array to test this method
For r = 0 To UBound(CodeData, 1) '## populate the array with random numbers
For c = 0 To UBound(CodeData, 2)
CodeData(r, c) = Application.WorksheetFunction.RandBetween(0, 100)
Next
Next
'## Drop this array in to the worksheet:
rng.Resize(UBound(CodeData, 2) + 1, UBound(CodeData, 1) + 1).Value = _
Application.WorksheetFunction.Transpose(CodeData)
End Sub
