vba vba转置类型不匹配错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23315252/
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
vba tranpose type mismatch error
提问by chrisc
I have been trying to transpose an array to paste records from a ADODB.recordset into an excel row. (The records are stored in a 2D Variant array). However, whenever I try something like the first row of the code below, I get a 13: type mismatch error code. I therefore tried to dig down into seeing exactly where the error lay, and built a simple integer 2d array. It appears that the transpose function can't do this transposition either without throwing a 13 error code. Research online seemed to suggest this error is caused when the number of elements being transposed exceed 64k but this can't be it below. Any thoughts?
我一直在尝试转置数组以将 ADODB.recordset 中的记录粘贴到 excel 行中。（记录存储在 2D Variant 数组中）。然而，每当我尝试类似下面代码的第一行时，我都会得到一个 13: type mismatch 错误代码。因此，我试图深入了解错误的确切位置，并构建了一个简单的整数二维数组。如果不抛出 13 错误代码，转置函数似乎也无法执行此转置。在线研究似乎表明当被转置的元素数量超过 64k 时会导致此错误，但这不能低于此错误。有什么想法吗？
varRecords = rs2.GetRows(3) ' rs2 is a ADOBD.Recordset
intNumReturned = UBound(varRecords, 2) + 1
intNumColumns = UBound(varRecords, 1) + 1
For intRow = 0 To intNumReturned - 1
For intColumn = 0 To intNumColumns - 1
Debug.Print varRecords(intColumn, intRow)
Next intColumn
Next intRow
Dim Destination As Range
Set Destination = Range("k1")
Dim i, j As Integer
'Destination.Resize(UBound(varRecords, 2) + 1, UBound(varRecords, 1) + 1).Value = Application.Transpose(varRecords) - COMMENTED OUT BECAUSE IT ERRORS TYPE 13 TYPE MISMATCH
Dim myarr(3, 4) As Integer
myarr(0, 1) = 4
myarr(2, 4) = 6
Dim myvar As Variant
Set myvar = Application.Transpose(myarr) ' - ERROR THROWN HERE
采纳答案by one angry researcher
I get the same error (13:type mismatch). An easy workaround would be to transpose the array yourself:
我得到同样的错误（13：类型不匹配）。一个简单的解决方法是自己转置数组：
Sub arrTest()
Dim myarr(3, 4) As Integer
myarr(0, 1) = 4
myarr(2, 4) = 6
Dim myvar As Variant
ReDim myvar(1 To UBound(myarr, 2), 1 To UBound(myarr, 1))
For i = 1 To UBound(myvar, 2)
For j = 1 To UBound(myarr, 1)
myvar(i, j) = myarr(j, i)
Next
Next
End Sub
Edit: Simoco is right, removing the "Set" keyword fixes the problem altogether.
编辑：Simoco 是对的，删除“Set”关键字完全解决了问题。
2nd Edit: The following works for me:
第二次编辑：以下对我有用：
Destination.Resize(UBound(varRecords, 2) + 1, UBound(varRecords, 1) + 1) = Application.Transpose(varRecords)
What kind of error do you receive when executing that first line of code?
执行第一行代码时会收到什么样的错误？
回答by Dave Pascoe
I ran into this same issue, and I'm betting you received the Type Mismatch for the same reason I did. One of your cells contains a string that is greater than 255 characters in length. I can't explain why Transpose can't handle a variant/string > 255, but it doesn't. Looks like an Excel bug. That's why coding your own transpose function works.
我遇到了同样的问题，我敢打赌你收到了类型不匹配的原因和我一样。您的一个单元格包含长度超过 255 个字符的字符串。我无法解释为什么 Transpose 无法处理 > 255 的变体/字符串，但事实并非如此。看起来像一个 Excel 错误。这就是编码您自己的转置函数有效的原因。
回答by Gregg Burns
I had this issue and the cause was from NULL's being stored in the array. A quick solve is to replace Null values in the array before transpose.
我遇到了这个问题，原因是 NULL 被存储在数组中。一个快速的解决方法是在转置之前替换数组中的 Null 值。
For i = 0 To 17
If IsNull(MetaAy(i, 0)) Then MetaAy(i, 0) = ""
Next i
Be careful using UBound if the last field contains a NULL. UBound() shows the array at the length the last non-null value was entered.
如果最后一个字段包含 NULL，请小心使用 UBound。UBound() 以输入最后一个非空值的长度显示数组。
回答by Steven
I also found the issue to be when an array element is too long. I like @one angry researcher's answer, wanted an array transpose function I could just use. I can't leave a comment, but after fixing some of the loops, I get this:
我还发现问题在于数组元素太长。我喜欢@one 愤怒的研究人员的回答，想要一个我可以使用的数组转置函数。我不能发表评论，但在修复了一些循环后，我得到了这个：
Function transposeArray(myarr As Variant) As Variant
Dim myvar As Variant
ReDim myvar(LBound(myarr, 2) To UBound(myarr, 2), LBound(myarr, 1) To UBound(myarr, 1))
For i = LBound(myarr, 2) To UBound(myarr, 2)
For j = LBound(myarr, 1) To UBound(myarr, 1)
myvar(i, j) = myarr(j, i)
Next
Next
transposeArray = myvar
End Function
回答by acontrario
While I didn't use the "set" I had the error 13. MarcZilla solution worked for me and is very fast even for transposing very large arrays. There are intrinsic limitations to my version of excel 2010 They can be found on https://support.microsoft.com/en-us/kb/177991?wa=wsignin1.0
虽然我没有使用“set”，但我遇到了错误 13。MarcZilla 解决方案对我有用，即使对于转置非常大的数组也非常快。我的 excel 2010 版本存在固有限制，可以在https://support.microsoft.com/en-us/kb/177991?wa=wsignin1.0上找到
