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