Moving Average VBA

[SurfsUp123]

Hi there,
I am trying to write a simple moving average function using VBA. Here is my code so far:

Function myMAAA(data, lag)

    'Define variables
    Dim nrows As Integer
    Dim RunSum As Double
    
    'variables for moving range
    Dim upperBound As Integer
    Dim lowerBound As Integer
    Dim smaRange As Range
    Dim movavRange As Range
    Dim firstRow As Integer
    Dim new_upperBound As Integer
    
    'count columns in range
    nrows = data.Rows.Count
    
    'defne array
    Dim DataArray() As Double
    ReDim DataArray(1 To nrows, 1)
    
    'store data into array
    For i = 1 To nrows
        DataArray(i, 1) = data.Cells(i, 1)
    Next i

    'define upper and lower bounds of moving range
    
    upperBound = data.Cells(1, 1).Offset(-lag).Row
    lowerBound = data.Cells(1, 1).Offset(0, 0).Row

    
    
    'define moving range
    Set smaRange = Range("M" & upperBound, "M" & lowerBound)
        
        
    RunSum = 0
         
    firstRow = Range("B2").Row
        
    'This is used to alter the range when the number of data points is less than the specified lag length
    If upperBound < firstRow Then
        
        new_upperBound = upperBound + (firstRow - upperBound)
            
        Set movavRange = Range("M" & new_upperBound, "M" & lowerBound)
        
        For w = 1 To movavRange.Rows.Count
        
            RunSum = RunSum + data.Cells(w - (movavRange.Rows.Count - 1), 1)
           
        Next w
        
        myMAAA = RunSum / movavRange.Rows.Count
        
    
    Else
        
        'calculate the sum of the cells in the moving range, once the lag length has been reached
        For w = 1 To smaRange.Rows.Count
        
            RunSum = RunSum + data.Cells(w - (smaRange.Rows.Count - 1), 1)
           
        Next w
        
        
        'divide the sum by the count to get average and output the average to the corresponding cell
        myMAAA = RunSum / smaRange.Rows.Count
        
    End If

End Function

One thing to note: firstRow = Range... must be set equal to the first row of the data set to work.

I have two problems that I would love some help with.

1. The first is that when the 'lag' input is greater than the number of cells between the first cell in the data range and the top row of the sheet, the code won't work. Is there a way to use an if statement or something so that the range doesn't go above the first cell in the data?

2. My second problem is that in order for the code to work, the first cell in the selected "data" input has to move down with the output. How can I make it so that I just select and anchor the whole "data" set and get it to loop through the rows internally? whenever I have tried this it has always just outputted the last iteration of the loop in every cell.

Any guidance/code ideas would be much appreciated!

 

[offthelip]

This seems a very hard way of doing something that is very easy to do with excel functions. I do this quite a bit and I like to be able to change the "lag" by changing the value in a cell. The way I do it is:
Put a series of number in column A. put the number 3 in cell B1 , then put this equation in B10:

=AVERAGE(OFFSET(A10,0,0,-B1))

Now alter the value in in B1 and you can changethe moving average to any value from 1 to 10