Ok, ich sehe du hast dir Gedanken dazu gemacht.

Hier mal meine Lösung (auf einem leeren Tabellenblatt testen / ausführen):

Geh es mal für dich gedanklich durch (es ähnelt deiner Herangehensweise).

Option Explicit

Sub Test()
  
  Dim wks As Excel.Worksheet
  Dim rng As Excel.Range
  Dim lngLastRow As Long
  
  Set wks = ThisWorkbook.Worksheets("Tabelle1")
  
  With wks
    
  '>> Beispieldaten erzeugen >>
    With .Range("A1:A1200")
      .NumberFormat = "General"
      .Formula = "=RANDBETWEEN(100,1000)"
      .Value = .Value 'Formeln zu Werten
    End With
  '<<
    
    'letzte Zelle mit Wert in Spalte A suchen
    '(hier nur zur Demonstation, wir wissen ja bereits die Anzahl der Spalte)
    lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    'den zu betrachenden Bereich referenzieren
    Set rng = .Range(.Cells(1, "A"), .Cells(lngLastRow, "A"))
    
  End With
  
  With rng
    'Ausgabe erfolgt in der Spalte daneben
    With .Offset(ColumnOffset:=1)
      .NumberFormat = "0.000"
      .Value = SMA(rng, 60) 'gleitenden Mittelwert im Intervall = 60 berechnen / ausgeben
    End With
  End With
  
End Sub

'//////////////////////////////////////////////////
'// Simple Moving Average
Public Function SMA(ValueList As Excel.Range, Interval As Long) As Variant
  
  If ValueList.Rows.Count > 1 Eqv ValueList.Columns.Count > 1 Then
  'matrix not allowed
    SMA = CVErr(XlCVError.xlErrRef)
    Exit Function
  ElseIf Not (2 <= Interval And Interval <= ValueList.Cells.Count) Then
  'interval out of range
    SMA = CVErr(XlCVError.xlErrNum)
    Exit Function
  End If
  
  Dim rngCell As Excel.Range
  Dim avntSMA() As Variant
  Dim dblSum As Double
  Dim i As Long
  
  ReDim avntSMA(1 To ValueList.Cells.Count)
  
  On Error GoTo ErrNotNumeric
    For i = 1 To ValueList.Cells.Count
      dblSum = dblSum + CDbl(ValueList.Cells(i).Value)
      If i >= Interval Then
        avntSMA(i) = dblSum / CDbl(Interval)
        dblSum = dblSum - CDbl(ValueList.Cells(1 + i - Interval).Value)
      Else
        avntSMA(i) = CVErr(XlCVError.xlErrNA)
      End If
    Next
  On Error GoTo 0
  
SafeExit:
  
  If ValueList.Rows.Count > ValueList.Columns.Count Then
    SMA = WorksheetFunction.Transpose(avntSMA)
  Else
    SMA = avntSMA
  End If
  
  Erase avntSMA
  
Exit Function
  
ErrNotNumeric:
  For i = i To ValueList.Cells.Count
    avntSMA(i) = CVErr(XlCVError.xlErrValue)
  Next
  GoTo SafeExit
End Function