Moving Average Calculation automation using VBA,VBA Teacher Sourav,Kolkata 08910141720

Sub movingaverages()
'
' Macro4 Macro
'

'
Sheets("MovingAverages").Select

    Range("B13").Select
    ActiveCell.FormulaR1C1 = "X"
    Range("B15").Select
    ActiveCell.FormulaR1C1 = "Data"
    Range("C13").Select
    ActiveCell.FormulaR1C1 = "mR"
    Range("C14").Select
    ActiveCell.FormulaR1C1 = "Moving"
    Range("C15").Select
    ActiveCell.FormulaR1C1 = "Range"
    Range("C17").Select
    ActiveCell.FormulaR1C1 = "=ABS(RC[-1]-R[-1]C[-1])"
    Range("C18").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"" "",ABS(RC[-1]-R[-1]C[-1]))"
    Range("C19").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"" "",ABS(RC[-1]-R[-1]C[-1]))"
    Range("C18").Select
    Selection.AutoFill Destination:=Range("C18:C216")
 
    Range("E14").Select
    ActiveCell.FormulaR1C1 = "Limits for the X Chart"
    Range("I14").Select
    ActiveCell.FormulaR1C1 = "Limit for mR Chart"
    Range("E15").Select
    ActiveCell.FormulaR1C1 = "UCL"
    Range("F15").Select
    ActiveCell.FormulaR1C1 = "X Avg"
    Range("G15").Select
    ActiveCell.FormulaR1C1 = "LCL"
    Range("H15").Select
    ActiveCell.FormulaR1C1 = "UCLmr"
    Range("I15").Select
    ActiveCell.FormulaR1C1 = "R Avg"
    Range("E16").Select
    ActiveCell.FormulaR1C1 = "=RC[1]+(RC[4]*2.66)"
    Range("E17").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-3]),"" "",R[-1]C)"
    Range("E17").Select
    Selection.AutoFill Destination:=Range("E17:E216")
  
    Range("F16").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-4]:R[200]C[-4])"
    Range("F17").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-4]),"" "",R[-1]C)"
    Range("F17").Select
    Selection.AutoFill Destination:=Range("F17:F216")
  
    Range("G16").Select
    ActiveCell.FormulaR1C1 = "=IF((RC[-1]-(RC[2]*2.66))<0 br="">    Range("G17").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-5]),"" "",R[-1]C)"
    Range("G17").Select
    Selection.AutoFill Destination:=Range("G17:G216")
    Range("G17:G216").Select
    Range("H16").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*3.27"
    Range("H17").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-6]),"" "",R[-1]C)"
    Range("H17").Select
    Selection.AutoFill Destination:=Range("H17:H216")
    Range("H17:H216").Select
    Range("I16").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[1]C[-6]:R[200]C[-6])"
    Range("I17").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-7]),"" "",R[-1]C)"
    Range("I17").Select
    Selection.AutoFill Destination:=Range("I17:I216")
    Range("I17:I216").Select
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "X Avg ="
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "UCLnp ="
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "LCLnp ="
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "R Avg ="
    Range("E7").Select
    ActiveCell.FormulaR1C1 = "UCLmr ="
    Range("E8").Select
    ActiveCell.FormulaR1C1 = "Moving Range = | x2 - x1| = ABS(B17-B16))"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[13]C[-4]:R[148]C[-4])"
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+(R[2]C*2.66)"
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=R[-2]C-(R[1]C*2.66)"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[11]C[-3]:R[145]C[-3])"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C*3.27"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "  average of all the x's =AVERAGE(B16:B216)"
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "  X Avg + (R Avg * 2.66)"
    Range("G5").Select
    ActiveCell.FormulaR1C1 = "  X Avg - (R Avg * 2.66)"
    Range("G6").Select
    ActiveCell.FormulaR1C1 = _
        "  average of the moving range (mR) values = AVERAGE(C17:C216)"
    Range("G7").Select
    ActiveCell.FormulaR1C1 = "  R Mean * 3.27"
    Range("E8").Select
    ActiveCell.FormulaR1C1 = "Moving Range = | x2 - x1| = ABS(B17-B16))"
    Call createchart
  
 End Sub



Sub createchart()
Sheets("MovingAverages").Select
Dim ch As Chart
 Range("A15:B200,E15:G200").Select


ActiveSheet.ChartObjects.Add Left:=5, Top:=4, Width:=500, Height:=200
Set ch = ActiveSheet.ChartObjects(1).Chart
With ch
    .ChartType = xlLineMarkers
    .SetSourceData Source:=ActiveSheet.Range("A15:A200,E15:G200"), PlotBy:=xlColumns

    With .Axes(xlValue)
    .HasMajorGridlines = True
    .HasMinorGridlines = False
   End With
End With


  

End Sub