Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub BMI()
- 'variable declaration
- Dim ws As Worksheet
- Dim ch As Chart
- Dim trend As Trendline
- Dim rng As Range
- Dim i As Long
- Set ws = ThisWorkbook.Sheets("Sheet2")
- Set rng = ws.Range("$A$3:$N$3")
- For i = 0 To 1
- With ws
- Set ch = ThisWorkbook.Sheets("Sheet3").Shapes.AddChart.Chart
- ch.ChartType = xlColumnClustered
- ch.SetSourceData Source:=Range(.Name & "!" & rng.Offset(i, 0).Address)
- Set trend = ch.SeriesCollection(1).Trendlines.Add(xlLinear)
- With trend.Border
- .ColorIndex = 33
- .Weight = xlMedium
- .LineStyle = xlDashDotDot
- End With
- 'Left & top are used to adjust the position of chart on sheet
- ch.ChartArea.Width = 500
- ch.ChartArea.Height = 300
- ch.ChartArea.Left = 200
- ch.ChartArea.Top = (i) * ch.ChartArea.Height + (50 * (i + 1))
- End With
- With ws
- ' Belllow code just to Add a title.
- ch.HasTitle = True
- With ch.ChartTitle
- .Text = ws.Range("A3").Offset(i, 0)
- ' Set the orientation of the title.
- ' Horizontal is the default, but you can change it
- ' to something else.
- .Orientation = XlOrientation.xlHorizontal
- ' You can format individual characters, or
- ' the entire title:
- With .Characters(1, 1).Font
- .Size = 24
- .Color = rgbDarkBlue
- End With
- .Characters(2).Font.Size = 14
- With .Format
- .Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1
- With .Shadow
- .ForeColor.ObjectThemeColor = msoThemeColorAccent4
- .Style = msoShadowStyleOuterShadow
- .OffsetX = 4
- .OffsetY = 4
- End With
- End With
- ' Make sure and leave room for the title in the chart.
- .IncludeInLayout = True
- End With
- End With
- ' code just to Add a title finish.
- Set ch = Nothing
- Set trend = Nothing
- Next
- Set rng = Nothing
- Set ws = Nothing
- End Sub
- Sub Weight_charts()
- 'variable declaration
- Dim ws As Worksheet
- Dim ch As Chart
- Dim trend As Trendline
- Dim rng As Range
- Dim i As Long
- Set ws = ThisWorkbook.Sheets("Sheet2")
- Set rng = ws.Range(ws.Name & "!" & "$A$2," & ws.Name & "!" & "$D$2:$O$2")
- For i = 1 To 2
- With ws
- Set ch = ThisWorkbook.Sheets("Weight_charts").Shapes.AddChart.Chart
- ch.ChartType = xlColumnClustered
- MsgBox rng.Offset(0, 0).Address & rng.Offset(i, 0).Address
- ch.SetSourceData Source:=ws.Range(rng.Offset(0, 0).Address & ", " & rng.Offset(i, 0).Address)
- ch.PlotBy = xlRows ' very important line to swap column and rows in chart
- ' ch.SetSourceData Source:=Range(.Name & "!" & rng.Offset(i, 0).Address)
- Set trend = ch.SeriesCollection(1).Trendlines.Add(xlLinear)
- With trend.Border
- .ColorIndex = 33
- .Weight = xlMedium
- .LineStyle = xlDashDotDot
- End With
- 'Left & top are used to adjust the position of chart on sheet
- ch.ChartArea.Width = 500
- ch.ChartArea.Height = 300
- ch.ChartArea.Left = 200
- ch.ChartArea.Top = (i) * ch.ChartArea.Height + (50 * (i + 1))
- End With
- With ws
- ' Belllow code just to Add a title.
- ch.HasTitle = True
- With ch.ChartTitle
- .Text = ws.Range("A2").Offset(i, 0)
- ' Set the orientation of the title.
- ' Horizontal is the default, but you can change it
- ' to something else.
- .Orientation = XlOrientation.xlHorizontal
- ' You can format individual characters, or
- ' the entire title:
- With .Characters(1, 1).Font
- .Size = 24
- .Color = rgbDarkBlue
- End With
- .Characters(2).Font.Size = 14
- With .Format
- .Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1
- With .Shadow
- .ForeColor.ObjectThemeColor = msoThemeColorAccent4
- .Style = msoShadowStyleOuterShadow
- .OffsetX = 4
- .OffsetY = 4
- End With
- End With
- ' Make sure and leave room for the title in the chart.
- .IncludeInLayout = True
- End With
- End With
- ' code just to Add a title finish.
- Set ch = Nothing
- Set trend = Nothing
- Next
- Set rng = Nothing
- Set ws = Nothing
- End Sub
Add Comment
Please, Sign In to add comment