[FIXED] How to change VBA one-dimensional array output from horizontal to vertical?

Issue

In the below VBA subroutine I generate an array ("ArrSim") of random numbers, such array measuring 10 rows x 3 columns. This array is pasted into cells A1:C10 of the active worksheet when running the sub. I then generate another array ("ArrRowAvg") where an average is calculated for each row of the ArrSim array. This works fine. The results of this 2nd array, ArrRowAvg, is pasted horizontally into the worksheet in cells E1:N1.

How do I change the code so that ArrRowAvg is instead pasted vertically, always two columns to the right of the ArrSim array? The below code is abbreviated & some of the input variables hard-coded for sake of simplicity; in the full code the user inputs the desired size of ArrSim. I just need to know how to make ArrRowAvg paste vertically. I´ve fiddled with the transpose and index functions with no luck.

Sub Testing()

'   Clear contents of active worksheet and move cursor to Cell A1
    Cells.Clear
    Range("A1").Select

'   Declarations of variables and arrays
    Dim i As Long, j As Integer
    Dim ArrSim() As Double
    Dim OutputSim As Range
     
'   Redimension array
    ReDim ArrSim(1 To 10, 1 To 3)
   
'   Set worksheet range
    Set OutputSim = ActiveCell.Range(Cells(1, 1), Cells(10, 3))
  
'   Fill ArrSim with random values
    For i = 1 To 10
        For j = 1 To 3
            ArrSim(i, j) = Application.RandBetween(0, 100)
        Next j
    Next i

'   Transfer ArrSim to Worksheet
    OutputSim.Value = ArrSim

'   Generate 1-dimensional array to store the row averages
    Dim ArrRowAvg, ArrRow
    ReDim ArrRowAvg(10 - 1)
        
'   Loop to calculate row averages from above ArrSim and feed into new array
    For i = 0 To UBound(ArrSim, 1) - 1
        ArrRow = Application.Index(ArrSim, i + 1, 0)
        ArrRowAvg(i) = WorksheetFunction.Average(ArrRow)
    Next i

'   Paste the array ArrRowAvg values starting one column to the right of OutputSim
    OutputSim.Offset(0, 1 + OutputSim.Columns.Count).Resize(1, UBound(ArrRowAvg) + 1).Value = ArrRowAvg
        
End Sub

Solution

Please, use this code line:

OutputSim.Offset(0, 1 + OutputSim.Columns.count).Resize(UBound(ArrRowAvg) + 1, 1).Value = Application.Transpose(ArrRowAvg)

instead of:

OutputSim.Offset(0, 1 + OutputSim.Columns.Count).Resize(1, UBound(ArrRowAvg) + 1).Value = ArrRowAvg

Answered By - FaneDuru

Answer Checked By - Marilyn (PHPFixing Volunteer)