Creating Moving Average chart automatically based on 4 series collection(Moving Averages,UCL,CL,LCL) using Excel VBA ,VBA Teacher Sourav,Kolkata 08910141720

Sub movingrangechartmacro()

Dim co As ChartObject
    Dim ct As Chart
    Dim scl As SeriesCollection
    Dim ser1 As Series
    Dim chartname As String

    Sheets("MovingRange").Select

       Set co = Worksheets("MovingRange").ChartObjects.Add(Range("H8").Left, Range("H8").Top, 2500, 500)
       
       'chartname = Worksheets("process_capability").Range("D15").Value
       chartname = "movingrange"
       co.Name = chartname
      ' MsgBox (co.Name)
       Set ct = co.Chart
       With ct
       .HasLegend = True
       .HasTitle = True
       .ChartTitle.Text = "Moving Range Chart"
       Set sc1 = .SeriesCollection
       Set ser1 = sc1.NewSeries
       With ser1
       .Name = Worksheets("MovingRange").Range("B1").Value
       .xvalues = Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown))
       .Values = Range(Worksheets("MovingRange").Range("B1").Offset(1, 0), Worksheets("MovingRange").Range("B1").End(xlDown))
       .ChartType = xlXYScatterLinesNoMarkers
       '.Select
       '.Smooth = True
        
       End With
       
       Set sc1 = .SeriesCollection
       Set ser1 = sc1.NewSeries
       With ser1
       .Name = Worksheets("MovingRange").Range("D1").Value
       .xvalues = Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown))
       .Values = Range(Worksheets("MovingRange").Range("D1").Offset(1, 0), Worksheets("MovingRange").Range("D1").End(xlDown))
       .ChartType = xlXYScatterLinesNoMarkers
       '.Select
       '.Smooth = True
        
       End With
       
       Set sc1 = .SeriesCollection
       Set ser1 = sc1.NewSeries
       With ser1
       .Name = Worksheets("MovingRange").Range("E1").Value
       .xvalues = Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown))
       .Values = Range(Worksheets("MovingRange").Range("E1").Offset(1, 0), Worksheets("MovingRange").Range("E1").End(xlDown))
       .ChartType = xlXYScatterLinesNoMarkers
       '.Select
       '.Smooth = True
        
       End With
       
       
       
       Set sc1 = .SeriesCollection
       Set ser1 = sc1.NewSeries
       With ser1
       .Name = Worksheets("MovingRange").Range("F1").Value
       .xvalues = Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown))
       .Values = Range(Worksheets("MovingRange").Range("F1").Offset(1, 0), Worksheets("MovingRange").Range("F1").End(xlDown))
       .ChartType = xlXYScatterLinesNoMarkers
       '.Select
       '.Smooth = True
        
       End With
      
       .Axes(xlValue).MajorGridlines.Select
         Selection.Delete
         Dim high, low As Long
         
      high = Round(((Application.WorksheetFunction.max(.SeriesCollection(2).Values))), 0)
      'MsgBox (high)
         low = Round(((Application.WorksheetFunction.min(.SeriesCollection(4).Values))), 0)
      'MsgBox (low)
      .Axes(xlValue).Select
      .Axes(xlValue).MinimumScale = low
      .Axes(xlValue).MaximumScale = high
      .Axes(xlValue).MajorUnit = 0.5
      Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown)).Select
    high = Round(Application.WorksheetFunction.max(Selection), 0)
    'MsgBox (high)
     low = Round(Application.WorksheetFunction.min(Selection), 0)
     .Axes(xlCategory).Select
      .Axes(xlCategory).MinimumScale = low
      .Axes(xlCategory).MaximumScale = high
      .Axes(xlCategory).MajorUnit = 1
     
     
     'X axis name
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Day Index"
'y-axis name
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Gas Use"

'coloring and designing the marker and the data series

Set sc1 = ct.SeriesCollection(1)
With sc1

    .Format.Line.Weight = 2 'Line.Weigth works ever

 .Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something
 .Format.Line.Visible = msoTrue
 .Format.Line.ForeColor.RGB = RGB(0, 0, 0) 'now it works

 .MarkerSize = 8
 .MarkerBackgroundColor = RGB(0, 0, 0) 'marker background

 .MarkerForegroundColor = RGB(0, 0, 0) 'marker foreground (lines around)
    
     End With
    
    
     
       
 'coloring UCL seriescollection
 Set sc1 = ct.SeriesCollection(2)
With sc1

    .Format.Line.Weight = 4 'Line.Weigth works ever

 .Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something
 .Format.Line.Visible = msoTrue
 .Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'now it works

 '.MarkerSize = 8
 '.MarkerBackgroundColor = RGB(0, 0, 0) 'marker background

 '.MarkerForegroundColor = RGB(0, 0, 0) 'marker foreground (lines around)
    
     End With

  'coloring LCL seriescollection
 Set sc1 = ct.SeriesCollection(4)
With sc1

    .Format.Line.Weight = 4 'Line.Weigth works ever

 .Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something
 .Format.Line.Visible = msoTrue
 .Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'now it works

 '.MarkerSize = 8
 '.MarkerBackgroundColor = RGB(0, 0, 0) 'marker background

 '.MarkerForegroundColor = RGB(0, 0, 0) 'marker foreground (lines around)
    
     End With
     
     
  'coloring CL seriescollection
 Set sc1 = ct.SeriesCollection(3)
With sc1

    .Format.Line.Weight = 4 'Line.Weigth works ever

 .Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something
 .Format.Line.Visible = msoTrue
 .Format.Line.ForeColor.RGB = RGB(0, 255, 0) 'now it works

 '.MarkerSize = 8
 '.MarkerBackgroundColor = RGB(0, 0, 0) 'marker background

 '.MarkerForegroundColor = RGB(0, 0, 0) 'marker foreground (lines around)
    
     End With


       
  End With
End Sub


The picture of chart to be created

The source Data

The video where the chart is created manually

https://www.youtube.com/watch?v=uGgd3iiQrUI

The link from stackoverflow which helped me regarding marker on series collection on a chart issue 

https://stackoverflow.com/questions/29675485/excel-vba-line-color-marker-line-color?answertab=votes#tab-top

Add two numbers present on sheet on User Alert using Google Apps Script

function addtwonumbersonsheet() {
 
var sh1=SpreadsheetApp.getActiveSpreadsheet();
var sheet1=sh1.getSheetByName("GAS_Sheet");
var num1=sheet1.getRange(2,1).getValue();
var num2=sheet1.getRange(2,2).getValue();  
var sh=SpreadsheetApp.getUi();
var response=sh.alert("Do you want to add the two number?",sh.ButtonSet.YES_NO);
if (response == sh.Button.YES)
{
  sheet1.getRange(2,3).setValue(num1+num2);
 
}

}

Taking input and Switch Case using Google Apps Script

//Declaring variables
var ui = SpreadsheetApp.getUi();
// Get the value from the user
var get_text= ui.prompt("Enter any number from 1 to 7").getResponseText();
//Convert the data type to integer
var get_num=parseInt(get_text);
//Comparing the entered number with days in a week
switch(get_num)
 {
   
   case 1:
        ui.alert("Today is sunday");
        break;   
   case 2:
        ui.alert("Today is monday");
        break;
   case 3:
        ui.alert("Today is tuesday");
        break;
   case 4:
        ui.alert("Today is Wednesday")
        break;
   case 5:
       ui.alert("Today is Thursday");
        break;
   case 6:
        ui.alert("Today is Friday");
        break;
   case 7:
        ui.alert("Today is Saturday");
        break;
   default:
         ui.alert("Entered number is not fall between 1 to 7")
 }






 

Toast Pop Up Using Google Apps Script

function greeting()
{
  SpreadsheetApp.getActiveSpreadsheet().toast("toast tester", "greeting", (number of seconds ,-1 for permanent)
 
 
 
}

Hello world using google apps script

function hello_world() {
  var my_message="hello world";
  SpreadsheetApp.getUi().alert(my_message);
}