Getting descripting statistics on varying number of groups with varying number of rows without data analysis toolpack using VBA

Sub descriptiveanal()
'Finds the last non-blank cell in a single row or column
  With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

Dim lRow As Long
Dim lCol As Long
Dim ColumnLetter As String
Dim area As String
Dim pasteloc As String

Sheets("descr").Select

   
    'Find the last non-blank cell in column A(1)
    lRow = Cells(1, 1).End(xlDown).Row
    If lRow = 1 Then
    lRow = 2
    End If
   
   
   
    'Find the last non-blank cell in row 1
    lCol = Cells(1, 1).End(xlToRight).Column
    ColumnLetter = Split(Cells(1, lCol).address, "$")(1)
    
  
   ' MsgBox ColumnLetter & lRow
 area = "A1:" & ColumnLetter & lRow

 lCol = lCol + 2
 ColumnLetter = Split(Cells(1, lCol).address, "$")(1)
    pasteloc = ColumnLetter & 1
    'MsgBox (pasteloc)
   
    'MsgBox (area)
    Range(area).Select
   
    Application.SendKeys "{ENTER}"
    Application.Run "ATPVBAEN.XLAM!Descr", ActiveSheet.Range(" " & area & " "), ActiveSheet.Range(" " & pasteloc & " "), "C", True, True, 1, 1, 95
   

   
   
    'this is more calculation
   
    Dim uslval As Double
   Dim lslsval As Double
  
   uslval = InputBox("enter value for usl")
   lslval = InputBox("enter value for usl")
    Dim i As Integer
    i = 1
   
   Dim address As String
   address = "A1"
      Sheets("descr").Select
      Range(address).Select
   While ActiveCell.Value <> ""
  
   Range(address).Select
  
  
  

  
   'Range("A1").Select
   Dim k As Long
   Dim minimum As Double
   Dim quartile1, quartile2, quartile3 As Double
   Dim max As Double
   Dim averageval As Double
   Dim stddevval As Double
   Dim varcoef As Double
  
  
  
    k = ActiveSheet.Range(address, ActiveSheet.Range(address).End(xlDown)).Rows.Count
   
   
    lRow = Cells(1, i).End(xlDown).Row
    If lRow = 1 Then
    lRow = 2
    End If
    area = GetText(address) & 2 & ":" & GetText(address) & lRow
   ' MsgBox (area)
    minimum = Application.min(Range(area))
    'MsgBox (minimum)
    quartile1 = Application.WorksheetFunction.Quartile(Range(area), 1)
    quartile2 = Application.WorksheetFunction.Quartile(Range(area), 2)
    quartile3 = Application.WorksheetFunction.Quartile(Range(area), 3)
   
    max = Application.min(Range(area))
   
    averageval = Application.WorksheetFunction.average(Range(area))
   
    stddevval = Application.WorksheetFunction.StDev(Range(area))
   
    area = GetText(address) & (lRow + 2)
   ' MsgBox (area)
   
   Range(area).Select
   ActiveCell.Value = "Count"
   ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = k
   ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "Minimum Value"
   ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = minimum
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "Quartile 1"
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = quartile1
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "Quartile 2"
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = quartile2
  
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "Quartile 3"
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = quartile3
  
   ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "Maximum Value"
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = max
  
   ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "Average"
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = averageval
  
   ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "Standard Deviation"
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = stddevval
  
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "Variation Coefficient"
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = (stddevval / averageval) * 100
  
     ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "USL"
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = uslval
  
     ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "LSL"
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = lslval
  
   
 
  
        ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "Cp"
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = (uslval - lslval) / (6 * stddevval)
  
         ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "Cpl"
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = (averageval - lslval) / (3 * stddevval)
  
            ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "Cpu"
    ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = (uslval - averageval) / (3 * stddevval)
  
               ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = "Cpk"
    ActiveCell.Offset(1, 0).Select
     Dim cpl As Double
   cpl = (averageval - lslval) / (3 * stddevval)
   Dim cpu As Double
   cpu = (uslval - averageval) / (3 * stddevval)
   ActiveCell.Value = Application.min(cpl, cpu)
  
  
   Range(address).Select
   ActiveCell.Offset(0, 1).Select
    
   address = Replace(ActiveCell.address, "$", "")
   Range(address).Select
   i = i + 1
  
    Wend
   
   
   
    ActiveSheet.Cells.Columns.AutoFit
   
   
      With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
  
End Sub

Function GetText(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetText = Result
End Function

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

Automating t-Test: Two-Sample Assuming Unequal Variances using VBA ,VBA Teacher Sourav,Kolkata 08910141720

Sub twosampletest()
'
' Macro1 Macro
'

'

 With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

Sheets("twosampletest").Select


Application.SendKeys "{ENTER}"
     Application.Run "ATPVBAEN.XLAM!Pttestv", ActiveSheet.Range("$A$1:$A$76"), _
        ActiveSheet.Range("$B$1:$B$76"), ActiveSheet.Range("$H$1:$W$25"), True, 0.05 _
        , 0
       
       
        ActiveSheet.Cells.Columns.AutoFit
   
      With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With
  
End Sub

Automating Anderson Darling Test using VBA,VBA Teacher Sourav,Kolkata 08910141720

Sub anderson()
'
' Macro1 Macro
'

'

Sheets("anderson").Select

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Enter the data into column E"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Average"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1]C[3]:R[198]C[3])"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Sigma"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "=STDEV(R[-2]C[3]:R[197]C[3])"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "n"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(R[-3]C[3]:R[196]C[3])"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "S"
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-5]C[9]:R[194]C[9])"
    Range("A8").Select
    ActiveCell.FormulaR1C1 = "AD"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "=-R[-3]C-R[-1]C/R[-3]C"
    Range("A9").Select
    ActiveCell.FormulaR1C1 = "AD*"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C*(1+0.75/R[-4]C+2.25/R[-4]C^2)"
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "p Value"
    Range("B10").Select
    ActiveCell.FormulaR1C1 = "=MAX(R[5]C:R[8]C)"
    Range("A14").Select
    ActiveCell.FormulaR1C1 = "p Value  Calculations"
    Range("A15").Select
    ActiveCell.FormulaR1C1 = "p"
    Range("A16").Select
    ActiveCell.FormulaR1C1 = "p"
    Range("A17").Select
    ActiveCell.FormulaR1C1 = "p"
    Range("A18").Select
    ActiveCell.FormulaR1C1 = "p"
    Range("B15").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(R[-6]C<13>= 0.6),EXP(1.2937-5.709*R[-6]C+0.0186*R[-6]C^ 2),0)"
    Range("B16").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(R[-7]C<0 .6="">=0.34),EXP(0.9177-4.279*R[-7]C-1.38*R[-7]C^2),0)"
    Range("B17").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(R[-8]C<0 .34="">=0.2),1-EXP(-8.318+42.796*R[-8]C-59.938*R[-8]C^2),0)"
    Range("B18").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R[-9]C<0 .2="" br="">    Range("B19").Select
 
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Value"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "i"
   'macro4
 
   Range("F1").Select
 
    ActiveCell.FormulaR1C1 = "i"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"""",1)"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"""",R[-1]C+1)"
    Range("F3").Select
    Selection.AutoFill Destination:=Range("F3:F153")
  
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Sorted"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-2]), NA(),SMALL(R2C[-2]:R201C[-2],RC[-1]))"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G151")
  
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "F(Xi)"
    With ActiveCell.Characters(Start:=1, Length:=3).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With ActiveCell.Characters(Start:=4, Length:=2).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = True
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("H2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-3]),"""",NORMDIST(RC[-1], R3C2, R4C2, TRUE))"
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H153")
  
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "1-F(Xi)"
    With ActiveCell.Characters(Start:=1, Length:=5).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With ActiveCell.Characters(Start:=6, Length:=2).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = True
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-4]), """", 1-RC[-1])"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I153")
  
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "1-F(Xn-i+1)"
    With ActiveCell.Characters(Start:=1, Length:=5).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With ActiveCell.Characters(Start:=6, Length:=6).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = True
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-5]),"""",SMALL(R2C[-1]:R201C[-1],RC[-4]))"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J153")
    Range("J2:J153").Select
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "S"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-6]),"""",(2*RC[-5]-1)*(LN(RC[-3])+LN(RC[-1])))"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K153")
    Range("K2:K153").Select
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "z"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-7]="""",NA(),NORMSINV((RC[-6]-0.3)/(R5C2+0.4)))"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L151")
    Range("L2:L151").Select
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*R4C2+R3C2"
    Range("M3").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*R4C2+R3C2"
    Range("M4").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*R4C2+R3C2"
    Range("M5").Select
 
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*R4C2+R3C2"
    Range("M3").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*R4C2+R3C2"
    Range("M4").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*R4C2+R3C2"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "-3"
    Range("N3").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("N4").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("N5").Select
  
    Dim ch As Shape
   Range("G2:G201,L2:L201").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
     Set ch = ActiveSheet.Shapes(1)
    ch.Name = "Chartanderson"
  
  
    Application.CommandBars("Format Object").Visible = False
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    With ActiveChart
'chart name
.HasTitle = True
.ChartTitle.Characters.Text = "Normal Probability Test"
'X axis name
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X"
'y-axis name
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Z"
End With

Range("A1").Select

End Sub

Check equal Variances with Bartlett's test with scatter plot using VBA,VBA Teacher Sourav,Kolkata 08910141720

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("BC23").Select
    ActiveCell.FormulaR1C1 = "group name"
  
    Range("BD24").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(R[-5]C[-54]), IF(R9C2=0,R[-5]C[-54],0)+IF(R9C2=1,LOG(R[-5]C[-54]+R10C2),0)+IF(R9C2=2,SQRT(R[-5]C[-54]+R10C2), 0),"" "")"
    Range("BD24").Select
    Selection.AutoFill Destination:=Range("BD24:BD1024")
    Range("BD24:BD1024").Select
    Range("BE24").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(R[-5]C[-54]), IF(R9C2=0,R[-5]C[-54],0)+IF(R9C2=1,LOG(R[-5]C[-54]+R10C2),0)+IF(R9C2=2,SQRT(R[-5]C[-54]+R10C2), 0),"" "")"
    Range("BE24").Select
    Selection.AutoFill Destination:=Range("BE24:BE1024")
    Range("BE24:BE1023").Select
    Range("BF24").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(R[-5]C[-54]), IF(R9C2=0,R[-5]C[-54],0)+IF(R9C2=1,LOG(R[-5]C[-54]+R10C2),0)+IF(R9C2=2,SQRT(R[-5]C[-54]+R10C2), 0),"" "")"
    Range("BF24").Select
    Selection.AutoFill Destination:=Range("BF24:BF1024")
    Range("BF24:BF1023").Select
    Range("BG24").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(R[-5]C[-54]), IF(R9C2=0,R[-5]C[-54],0)+IF(R9C2=1,LOG(R[-5]C[-54]+R10C2),0)+IF(R9C2=2,SQRT(R[-5]C[-54]+R10C2), 0),"" "")"
    Range("BG24").Select
    Selection.AutoFill Destination:=Range("BG24:BG1024")
    Range("BG24:BG1023").Select
    Range("BH24").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(R[-5]C[-54]), IF(R9C2=0,R[-5]C[-54],0)+IF(R9C2=1,LOG(R[-5]C[-54]+R10C2),0)+IF(R9C2=2,SQRT(R[-5]C[-54]+R10C2), 0),"" "")"
    Range("BH24").Select
    Selection.AutoFill Destination:=Range("BH24:BH1024")
    Range("BH24:BH1023").Select
   
   

' Macro2 Macro
'

'
    Range("BC21").Select
    ActiveCell.FormulaR1C1 = "1 if data present"
    Range("BD21").Select
    ActiveCell.FormulaR1C1 = "=IF(COUNT(R[3]C:R[1002]C)>0.5,1,""-"")"
  
    Selection.AutoFill Destination:=Range("BD21:DA21"), Type:=xlFillDefault
   
   



'
    Range("BC8").Select
    ActiveCell.FormulaR1C1 = "mean for graph"
    Range("BD8").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[16]C:R[1015]C)"
    Range("BE8").Select
    ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(R[16]C),AVERAGE(R[16]C:R[1015]C),RC56)"
   
    Selection.AutoFill Destination:=Range("BE8:DA8"), Type:=xlFillDefault
 

'

'

'
    Range("BC9").Select
    ActiveCell.FormulaR1C1 = "stdev for graph"
    Range("BD9").Select
    ActiveCell.FormulaR1C1 = "=STDEV(R[15]C:R[1014]C)"
    Range("BE9").Select
    ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(R[15]C),STDEV(R[15]C:R[1014]C),RC56)"
   
    Selection.AutoFill Destination:=Range("BE9:DA9"), Type:=xlFillDefault

'
' Macro5 Macro
'

'
    Range("BC13").Select
    ActiveCell.FormulaR1C1 = "variance X (n-1)"
    Range("BD13").Select
    ActiveCell.FormulaR1C1 = "=IF(R[8]C=1,VAR(R[11]C:R[1010]C)*(R[1]C-1),""-"")"
  
    Range("BD13").Select
    Selection.AutoFill Destination:=Range("BD13:DA13"), Type:=xlFillDefault
   

Range("BA14").Select
ActiveCell.FormulaR1C1 = "ln weighted average variance"

'
   
    Range("BB14").Select
    ActiveCell.FormulaR1C1 = "=LN(SUM(R[-1]C[2]:R[-1]C[51])/R[1]C)"
    Range("BC14").Select
    ActiveCell.FormulaR1C1 = "n"
    Range("BD14").Select
    ActiveCell.FormulaR1C1 = "=IF(R[7]C=1,COUNT(R[10]C:R[1009]C),""-"")"
    Range("BD14").Select
    Selection.AutoFill Destination:=Range("BD14:DA14"), Type:=xlFillDefault



'
    Range("BA15").Select
    ActiveCell.FormulaR1C1 = "degrees of freedom"
    Range("BB15").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[2]:R[-1]C[51])-SUM(R[6]C[2]:R[6]C[51])"
    Range("BC15").Select
    ActiveCell.FormulaR1C1 = "(n-1) * ln(var)"
    Range("BD15").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R[6]C=1,(R[-1]C-1)*LN(VAR(R[9]C:R[1008]C)),""-"")"
   
   
    Selection.AutoFill Destination:=Range("BD15:DA15"), Type:=xlFillDefault
   


'
    Range("BA16").Select
    ActiveCell.FormulaR1C1 = "weighted sum of ln variance"
    Range("BB16").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[2]:R[-1]C[51])"
    Range("BC16").Select
    ActiveCell.FormulaR1C1 = "1/(n-1)"
    Range("BD16").Select
    ActiveCell.FormulaR1C1 = "=IF(R[5]C=1,1/(R[-2]C-1),""-"")"
   
   
   
    Selection.AutoFill Destination:=Range("BD16:DA16"), Type:=xlFillDefault
   


'
    Range("BA17").Select
    ActiveCell.FormulaR1C1 = "test statistic"
    Range("BB17").Select
    ActiveCell.FormulaR1C1 = "=R[-2]C*R[-3]C-R[-1]C"
    Range("BA18").Select
    ActiveCell.FormulaR1C1 = "correction factor"
    Range("BB18").Select
    ActiveCell.FormulaR1C1 = _
        "=1+(1/(3*(SUM(R[3]C[2]:R[3]C[51])-1)))*(SUM(R[-2]C[2]:R[-2]C[51])-(1/R[-3]C))"
    Range("BA19").Select
    ActiveCell.FormulaR1C1 = "corrected test statistic"
    Range("BB19").Select
    ActiveCell.FormulaR1C1 = "=R[-2]C/R[-1]C"
    Range("BA21").Select
    ActiveCell.FormulaR1C1 = "P"
    Range("BB21").Select
    ActiveCell.FormulaR1C1 = "=CHIDIST(R[-2]C,SUM(RC[2]:RC[51])-1)"
  

   

'

'
   
    Range("A9").Select
    ActiveCell.FormulaR1C1 = _
        "enter ""O"" for untransformed data, ""1"" for log-transformed, ""2"" for square-root transformed:"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "enter a constant to add to each number :"
    Range("B10").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("A13").Select
    ActiveCell.FormulaR1C1 = "P-value:"
    Range("B13").Select
    ActiveCell.FormulaR1C1 = "=R[8]C[52]"
   
    Range("A15").Select
    ActiveCell.FormulaR1C1 = "means of transformed data:"
    Range("B15").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(R[9]C[54]),AVERAGE(R[9]C[54]:R[1008]C[54]),"" "")"
    Range("B15").Select
    Selection.AutoFill Destination:=Range("B15:F15"), Type:=xlFillDefault
    Range("B15:F15").Select
    Range("A16").Select
    ActiveCell.FormulaR1C1 = "standard deviations of transformed data:"
    Range("B16").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(R[8]C[54]),STDEV(R[8]C[54]:R[1007]C[54]),"" "")"
    Range("B16").Select
   
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(R[8]C[54]),STDEV(R[8]C[54]:R[1007]C[54]),"" "")"
  
    Selection.AutoFill Destination:=Range("B16:F16"), Type:=xlFillDefault
   
   
     Dim ch As Shape
    Range("BD8:DA9").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
     Set ch = ActiveSheet.Shapes(1)
    ch.Name = "Chartscatter"
   
    ActiveChart.SetSourceData Source:=Range("statbartletts.xls!$BD$8:$DA$9")
    ActiveSheet.Shapes("Chartscatter").IncrementLeft -2.25
    ActiveSheet.Shapes("Chartscatter").IncrementTop -0.75
    Application.CommandBars("Format Object").Visible = False
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    With ActiveChart
'chart name
.HasTitle = True
.ChartTitle.Characters.Text = "Chartscatter"
'X axis name
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Mean of Transformed Data"
'y-axis name
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Standard Deviation"
End With
   
 
  
   
End Sub

Run descriptive statistics automatically on several groups of data using data analysis toolpack using VBA,VBA Teacher Sourav,Kolkata 08910141720

Sub descriptiveanal()
'Finds the last non-blank cell in a single row or column
  With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

Dim lRow As Long
Dim lCol As Long
Dim ColumnLetter As String
Dim area As String
Dim pasteloc As String

Sheets("descr").Select

   
    'Find the last non-blank cell in column A(1)
    lRow = Cells(1, 1).End(xlDown).Row
    If lRow = 1 Then
    lRow = 2
    End If
   
   
   
    'Find the last non-blank cell in row 1
    lCol = Cells(1, 1).End(xlToRight).Column
    ColumnLetter = Split(Cells(1, lCol).Address, "$")(1)
    
  
   ' MsgBox ColumnLetter & lRow
 area = "A1:" & ColumnLetter & lRow

 lCol = lCol + 2
 ColumnLetter = Split(Cells(1, lCol).Address, "$")(1)
    pasteloc = ColumnLetter & 1
    'MsgBox (pasteloc)
   
    'MsgBox (area)
    Range(area).Select
   
    Application.SendKeys "{ENTER}"
    Application.Run "ATPVBAEN.XLAM!Descr", ActiveSheet.Range(" " & area & " "), ActiveSheet.Range(" " & pasteloc & " "), "C", True, True, 1, 1, 95
   

    ActiveSheet.Cells.Columns.AutoFit
   
      With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With
  
End Sub