Hi all,
I'm having a problem with using worksheet functions on arrays stored in vba. I use the following code to generate my array from results of iterations in Excel:
I generate the two dimensional array without any issues - the problems start when I try and get the average value in the first column (last code line). I cannot seem to get the result to stay as a value.
Endgame is to generate three statistics from the array for each column: The average, the 25th Percentile and the 75th Percentile, percentiles using the LARGE function. These then to be stored as arrays and pasted into Excel.
Can anybody give me a hand finsihing off the code?
Many thanks
I'm having a problem with using worksheet functions on arrays stored in vba. I use the following code to generate my array from results of iterations in Excel:
Code:
Sub PopArray()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Runs As Integer
Dim Cols As Integer
Dim Y As Double
Dim X As Double
Dim L1A As Variant
Cols = 11
Runs = Range("Runs").Value
ReDim L1A(Runs, Cols)
For i = 1 To Runs
Application.CalculateFull
For j = 1 To Cols
X = Range("L1_START").Offset(0, j).Value
L1A(i, j) = X
Next j
Next i
Y = WorksheetFunction.Average(L1A(1, 1), L1A(1, Runs))
End Sub
I generate the two dimensional array without any issues - the problems start when I try and get the average value in the first column (last code line). I cannot seem to get the result to stay as a value.
Endgame is to generate three statistics from the array for each column: The average, the 25th Percentile and the 75th Percentile, percentiles using the LARGE function. These then to be stored as arrays and pasted into Excel.
Can anybody give me a hand finsihing off the code?
Many thanks