寻求可迭代计算EMA的Excel VBA宏开发方案

Here's a tailored macro that automates the exact formula setup you described. It uses row number variables to dynamically generate each unique formula, and loops through all the required iterations:

Sub GenerateEMA()
    Dim ws As Worksheet
    Dim startRow As Long
    Dim endAvgRow As Long
    Dim emaStartRow As Long
    
    ' Set the target worksheet (replace "Sheet1" with your actual sheet name if needed)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Iterate through each starting row, stepping 70 rows each time
    For startRow = 153 To 8973 Step 70
        ' Calculate the end row of the 12-row average range
        endAvgRow = startRow + 11
        ' Calculate the row immediately below the average cell for the first EMA formula
        emaStartRow = endAvgRow + 1
        
        ' Write the average formula to column E at the end of the range
        ws.Range("E" & endAvgRow).Formula = "=AVERAGE(D" & startRow & ":D" & endAvgRow & ")"
        
        ' Write the initial EMA formula to the cell below the average
        ws.Range("E" & emaStartRow).Formula = "=(2/13)*D" & emaStartRow & "+(11/13)*E" & endAvgRow & ""
    Next startRow
    
    MsgBox "All EMA formulas have been added successfully!", vbInformation
End Sub

How It Works: #

• Worksheet Setup: First, we define which worksheet to work with (update "Sheet1" to your sheet's name if necessary).
• Loop Through Iterations: The loop starts at row 153, then jumps 70 rows each time until it reaches the final starting row of 8973.
• Average Formula: For each iteration, we calculate the end of the 12-row range (start row +11) and write the AVERAGE formula to column E at that end row.
• Initial EMA Formula: Right below the average cell, we write the first EMA formula using dynamic row numbers—this is the one you can drag down to fill the rest of the EMA values for that segment.
• Completion Message: A pop-up confirms when all formulas are added.

Usage Instructions: #

1. Open your Excel file with the stock data.
2. Press Alt + F11 to open the VBA Editor.
3. Insert a new module: Right-click your workbook in the Project Explorer > Insert > Module.
4. Paste the code above into the module.
5. Adjust the worksheet name if needed (replace "Sheet1").
6. Run the macro: Press F5 or use the Run button in the editor.

Once the macro finishes, you can drag each initial EMA formula (in column E, right below the average cell) down as far as needed to extend the EMA sequence for that segment.

内容的提问来源于stack exchange，提问作者mathwandererw