Excel VBA: Application.Tranpose on an array gives Run-time error 438

Question

I have a VBA code that gets data from a column of a structured table and puts it in a column of another structured table like this:

Dim TopNTickers As Variant
Dim NoOfTickers As Integer
Dim OutputTickers As Range
TopNTickers = Application.Transpose(Range("PortfolioTbl[Ticker]"))
NoOfTickers = UBound(TopNTickers)

With Range("TopNTickersTbl").ListObject.ListColumns("Ticker").Range
    Set OutputTickers = .Parent.Range(.Cells(2, 1), .Cells(NoOfTickers + 1, 1))
End With

OutputTickers = Application.Tranpose(TopNTickers)

The first Transpose worked fine. It copied the data from the Ticker column of PortfolioTbl table into the 1D TopNTickers array.

The next part selects a subset of the Ticker column of the TopNTickersTbl table. It seems to work fine.

The last part gives me error:

Object doesn't support property or method

I have also tried to replace OutputTickers with OutputTickers.Value but the same error is there.

Note:

The code above has been simplified. In its real version, I had a few other lines that deleted the content of TopNTickersTbl then add the number of lines to it equal to the size of the array TopNTickers, in preparation for the data-write-out stage. The same error happened in both versions, and I am quite sure the extra code is not related to this error.

Answer 1

That's a strange one for sure.

I was able to replicate your error. It looks like we can't put an array into a table with fewer rows than the array.

We can either add the empty rows to the table first, or add the array items one by one, allowing the Table to adjust its size as each item is added.

I did the latter, and it worked.

Public Sub Test1()

Dim TopNTickers As Variant
Dim NoOfTickers As Integer
Dim OutputTickers As Range
TopNTickers = Application.Transpose(Range("PortfolioTbl[Ticker]"))
NoOfTickers = UBound(TopNTickers)

With Range("TopNTickersTbl").ListObject.ListColumns("Ticker").Range
    Set OutputTickers = Range("TopNTickersTbl").ListObject.ListColumns("Ticker").Range.Parent.Range(.Cells(2, 1), .Cells(NoOfTickers + 1, 1))
End With

Dim i As Integer
For i = 1 To NoOfTickers
    OutputTickers.Cells(i, 1) = TopNTickers(i)
Next i

End Sub

Edit: I don't know your use case exactly, but you might find it simpler all round to use PowerQuery to get the TopN items. Or even just a pivot table.