How to sum some elements of another array into a new one

Hi All,

I have always used range references on worksheets for calculations, but am now starting to use arrays to work with data, due to how much faster they can be. I am trying to get "SMA_MIN_Array" to calculate a 50 period moving average of the preceding 50 values of the array named vArray. Does anybody know how to accomplish it? Here is what I have so far, and I cannot get it to work. The "Sum(vArray(j,j+49)/50) is the culprit. I know that isn't right, but I just don't know how to translate this into summing items 1 through 50 and dividing the total by 50. Any ideas? Here is a snippet of some code that I have tried: (If you live in Dallas, I will buy you a drink of your choice for help on this problem!)


Thanks,
Nick

Dim Last_Row As Long
Last_Row = Mainwb.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

Dim rng As Range
Set rng = Mainwb.Worksheets("Sheet1").Range("F5:F" & Last_Row)

Dim vArray() As Variant
vArray = rng

Dim i As Long
For i = LBound(vArray, 1) To UBound(vArray, 1)
Next

    Dim newWorkbook As Workbook
    Set newWorkbook = Application.Workbooks.Add
    
    With newWorkbook.Sheets("Sheet1")
    .Range(.Cells(5, 8), .Cells(Last_Row, 8)).Value = vArray
    End With


Dim SMA_MIN_Array() As Variant
ReDim SMA_MIN_Array(1 To Last_Row - 50, 1 To 1)
For j = 1 To Last_Row - 50
SMA_MIN_Array(j, 1) = Sum(vArray(j, j + 49))
Next


With newWorkbook.Sheets("Sheet1")
.Range(.Cells(55, 10), .Cells(Last_Row, 10)).Value = SMA_MIN_Array

End With

I have to use arrays due to the sheer volume of the calculations. What you see is only the tip of the iceberg. I need to run hundreds of thousands of loop iterations with hundreds of thousands of calcs. Unfortunately I need to do this in arrays for the processing speed and flexibility. Any ideas? The more calcs I can do in memory without referencing a sheet the easier and faster this will be.