vba tranpose type mismatch error

3
votes

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?

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
5
arraysexcelvbatranspose
remove Set keyword: myvar = Application.Transpose(myarr) – Dmitry Pavliv
You should add more infomration to this question if you are looking for assistance with the first line. Otherwise, the posted answer is correct and should be accepted. For the commented code, perhaps it will be helpful if you show how varRecords is instantiated, assigned, and its dimensions. Also please indicate what specific error message you get on that line. Cheers. – David Zemens
Ok, will edit existing question – chrisc
As you pull data from ADODB recordset to array, the reason for the type mismatch error might be that some empty values are stored as null in the resulting array. Transpose cannot process nulls. – ZygD

5 Answers

7
votes

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.

3
votes

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. 

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.

3
votes

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: 

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
0
votes

I get the same error (13:type mismatch). An easy workaround would be to transpose the array yourself:

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.

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?

0
votes

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