.Transpose and .Index failing after 16.60 update

user_18941673 0
Viewed 84

After the Excel 16.60 update for mac I cannot get .transpose and .index to work in any macros. The error is

"Run-time error '1004': Method 'Transpose' of object 'WorksheetFunction' failed".

Here's code that I wrote for a simple test:

Sub Test_Here()
    Dim testString() As String
    Dim xVar As Variant
    ReDim testString(3, 3)
    testString(0, 0) = "test11": testString(0, 1) = "test12": testString(0, 2) = "test13": testString(0, 3) = "test14"
    testString(1, 0) = "test21": testString(1, 1) = "test22": testString(1, 2) = "test23": testString(1, 3) = "test24"
    testString(2, 0) = "test31": testString(2, 1) = "test32": testString(2, 2) = "test33": testString(2, 3) = "test34"
    testString(3, 0) = "test41": testString(3, 1) = "test42": testString(3, 2) = "test43": testString(3, 3) = "test44"
    Worksheets("Sheet1").Range("A1").Resize(UBound(testString, 2) + 1, UBound(testString, 1) + 1).Value = WorksheetFunction.Transpose(testString)
End Sub
transpose indexing vba excel
1 Answers

VBA's Transpose has been a cause of intermittent issues on Windows and Mac for a very long time. It rendered Korean-ish when I tried your code. (I'm not sure if it was real words, but definitely based on the Korean written language!)

Instead of trying to get something magical to happen with VBA, I've provided a function that manually transposes an array. When you create the object testString, you have to create it as a Variant for this code to work.

Sub Test_Here()
    
    Dim testString() As Variant
    Dim xVar As Variant
    
    ReDim testString(3, 3)
    
    testString(0, 0) = "test11": testString(0, 1) = "test12": testString(0, 2) = "test13": testString(0, 3) = "test14"
    testString(1, 0) = "test21": testString(1, 1) = "test22": testString(1, 2) = "test23": testString(1, 3) = "test24"
    testString(2, 0) = "test31": testString(2, 1) = "test32": testString(2, 2) = "test33": testString(2, 3) = "test34"
    testString(3, 0) = "test41": testString(3, 1) = "test42": testString(3, 2) = "test43": testString(3, 3) = "test44"
    
    testString = TranspA(testString)                                      'transpose the array

    'add to worksheet
    Worksheets("Sheet1").Range("A1").Resize(UBound(testString, 2) + 1, UBound(testString, 1) + 1).Value = testString

End Sub
   

Public Function TranspA(arD As Variant) As Variant 'arD as an array of data
    Dim X As Long
    Dim Y As Long
    Dim Xu As Long
    Dim Yu As Long
    Dim tempA As Variant  'temporary array holder
    
    Xu = UBound(arD, 2)
    Yu = UBound(arD, 1)
    ReDim tempA(Xu, Yu)
    For X = 0 To Xu
        For Y = 0 To Yu
            tempA(X, Y) = arD(Y, X)
        Next Y
    Next X
    TranspA = tempA
    
End Function
user_53290730
Related