SHARE
TWEET

Untitled




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
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.