Asign Array Variant to Column

[William C. Smith]

Assigning a one dimensional variant array to cells in a row works, but when
the code assigns the array to cells in a column, each cell in the range
contains the first element from the variant array! I cannot find a way of
transposing the variant

This code works: The cells are in a row
count = UBound(answer, 1)
Range(ActiveCell, ActiveCell.Offset(0, count-1)).Formula = answer

This code assigns the first element of the variant array to each cell in
the column range.
count = UBound(answer, 1)
Range(ActiveCell, ActiveCell.Offset(count-1, 0)).Formula = answer

Is there a way of making Excel asign the elements of the array to a column?

 

[Rob van Gelder]

User defined function which is to be used with an array formula:

Function testit(invalue As Integer) As Variant()
Dim varItems() As Variant, i As Long, j As Long

With Application.Caller
ReDim varItems(.Rows.Count - 1, .Columns.Count - 1)

For i = 0 To .Rows.Count - 1
For j = 0 To .Columns.Count - 1
varItems(i, j) = i * j * invalue
Next
Next
End With

testit = varItems()
End Function

Assigning a one dimensional variant array to cells in a row works, but when
the code assigns the array to cells in a column, each cell in the range
contains the first element from the variant array! I cannot find a way of
transposing the variant

This code works: The cells are in a row
count = UBound(answer, 1)
Range(ActiveCell, ActiveCell.Offset(0, count-1)).Formula = answer

This code assigns the first element of the variant array to each cell in
the column range.
count = UBound(answer, 1)
Range(ActiveCell, ActiveCell.Offset(count-1, 0)).Formula = answer

Is there a way of making Excel asign the elements of the array to a

column?

 

[Harlan Grove]

This code assigns the first element of the variant array to each cell in
the column range.
count = UBound(answer, 1)
Range(ActiveCell, ActiveCell.Offset(count-1, 0)).Formula = answer

Is there a way of making Excel asign the elements of the array to a column?

ActiveCell.Resize(UBound(answer), 1).Formula = _
Application.WorksheetFunction.Transpose(answer)

should work.

 

[William C. Smith]

ActiveCell.Resize(UBound(answer), 1).Formula = _
Application.WorksheetFunction.Transpose(answer)

Application.WorksheetFunction.Transpose(answer) works. Thank you.

Bill