Public Pastes
SHARE
TWEET

Untitled

a guest Jan 16th, 2019 62 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub BMI()
  2.      'variable declaration
  3.         Dim ws As Worksheet
  4.         Dim ch As Chart
  5.         Dim trend As Trendline
  6.         Dim rng As Range
  7.         Dim i As Long
  8.  
  9.     Set ws = ThisWorkbook.Sheets("Sheet2")
  10.     Set rng = ws.Range("$A$3:$N$3")
  11.  
  12.     For i = 0 To 1
  13.     With ws
  14.  
  15.         Set ch = ThisWorkbook.Sheets("Sheet3").Shapes.AddChart.Chart
  16.         ch.ChartType = xlColumnClustered
  17.         ch.SetSourceData Source:=Range(.Name & "!" & rng.Offset(i, 0).Address)
  18.         Set trend = ch.SeriesCollection(1).Trendlines.Add(xlLinear)
  19.             With trend.Border
  20.                 .ColorIndex = 33
  21.                 .Weight = xlMedium
  22.                 .LineStyle = xlDashDotDot
  23.             End With
  24.         'Left & top are used to adjust the position of chart on sheet
  25.  
  26.  
  27.         ch.ChartArea.Width = 500
  28.         ch.ChartArea.Height = 300
  29.         ch.ChartArea.Left = 200
  30.         ch.ChartArea.Top = (i) * ch.ChartArea.Height + (50 * (i + 1))
  31.     End With
  32.  
  33.    With ws
  34.          ' Belllow code just to  Add a title.
  35.         ch.HasTitle = True
  36.         With ch.ChartTitle
  37.             .Text = ws.Range("A3").Offset(i, 0)
  38.  
  39.             ' Set the orientation of the title.
  40.             ' Horizontal is the default, but you can change it
  41.             ' to something else.
  42.             .Orientation = XlOrientation.xlHorizontal
  43.  
  44.             ' You can format individual characters, or
  45.             ' the entire title:
  46.             With .Characters(1, 1).Font
  47.                 .Size = 24
  48.                 .Color = rgbDarkBlue
  49.  
  50.             End With
  51.  
  52.             .Characters(2).Font.Size = 14
  53.  
  54.             With .Format
  55.  
  56.                 .Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1
  57.                 With .Shadow
  58.                     .ForeColor.ObjectThemeColor = msoThemeColorAccent4
  59.                     .Style = msoShadowStyleOuterShadow
  60.                     .OffsetX = 4
  61.                     .OffsetY = 4
  62.                 End With
  63.             End With
  64.  
  65.             ' Make sure and leave room for the title in the chart.
  66.             .IncludeInLayout = True
  67.         End With
  68.     End With
  69.      ' code just to  Add a title finish.
  70.  
  71.  
  72.     Set ch = Nothing
  73.     Set trend = Nothing
  74.  
  75. Next
  76.  
  77. Set rng = Nothing
  78. Set ws = Nothing
  79.  
  80. End Sub
  81.    
  82. Sub Weight_charts()
  83.  
  84.    'variable declaration
  85.     Dim ws As Worksheet
  86.     Dim ch As Chart
  87.     Dim trend As Trendline
  88.     Dim rng As Range
  89.     Dim i As Long
  90.  
  91.     Set ws = ThisWorkbook.Sheets("Sheet2")
  92.     Set rng = ws.Range(ws.Name & "!" & "$A$2," & ws.Name & "!" & "$D$2:$O$2")
  93.  
  94.     For i = 1 To 2
  95.     With ws
  96.  
  97.         Set ch = ThisWorkbook.Sheets("Weight_charts").Shapes.AddChart.Chart
  98.         ch.ChartType = xlColumnClustered
  99.  
  100.        MsgBox rng.Offset(0, 0).Address & rng.Offset(i, 0).Address
  101.  
  102.        ch.SetSourceData Source:=ws.Range(rng.Offset(0, 0).Address & ", " & rng.Offset(i, 0).Address)
  103.        ch.PlotBy = xlRows       ' very important line to swap column and rows in chart
  104.        ' ch.SetSourceData Source:=Range(.Name & "!" & rng.Offset(i, 0).Address)
  105.         Set trend = ch.SeriesCollection(1).Trendlines.Add(xlLinear)
  106.             With trend.Border
  107.                 .ColorIndex = 33
  108.                 .Weight = xlMedium
  109.                 .LineStyle = xlDashDotDot
  110.             End With
  111.         'Left & top are used to adjust the position of chart on sheet
  112.  
  113.  
  114.         ch.ChartArea.Width = 500
  115.         ch.ChartArea.Height = 300
  116.         ch.ChartArea.Left = 200
  117.         ch.ChartArea.Top = (i) * ch.ChartArea.Height + (50 * (i + 1))
  118.     End With
  119.  
  120.    With ws
  121.          ' Belllow code just to  Add a title.
  122.         ch.HasTitle = True
  123.         With ch.ChartTitle
  124.             .Text = ws.Range("A2").Offset(i, 0)
  125.  
  126.             ' Set the orientation of the title.
  127.             ' Horizontal is the default, but you can change it
  128.             ' to something else.
  129.             .Orientation = XlOrientation.xlHorizontal
  130.  
  131.             ' You can format individual characters, or
  132.             ' the entire title:
  133.             With .Characters(1, 1).Font
  134.                 .Size = 24
  135.                 .Color = rgbDarkBlue
  136.  
  137.             End With
  138.  
  139.             .Characters(2).Font.Size = 14
  140.  
  141.             With .Format
  142.  
  143.                 .Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1
  144.                 With .Shadow
  145.                     .ForeColor.ObjectThemeColor = msoThemeColorAccent4
  146.                     .Style = msoShadowStyleOuterShadow
  147.                     .OffsetX = 4
  148.                     .OffsetY = 4
  149.                 End With
  150.             End With
  151.  
  152.             ' Make sure and leave room for the title in the chart.
  153.             .IncludeInLayout = True
  154.         End With
  155.     End With
  156.      ' code just to  Add a title finish.
  157.  
  158.  
  159.     Set ch = Nothing
  160.     Set trend = Nothing
  161.  
  162. Next
  163.  
  164. Set rng = Nothing
  165. Set ws = Nothing
  166.  
  167. End Sub
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top