charting with vba

[choubix]

hello,

I am trying to plot a few series on a chart.
Column A = Dates
Column B = Weight
Column C = Body Fat
Column D = Muscle Mass


I am a bit clueless on :
- how to handle the series through arrays
- plot the arrays in the chart


can someone point me in the right direction please?

thx

[Bob Phillips]

I have made quite a few assumptions, namely:

- you wanted a line chart
-weight would be in a weight unit such as kilos, but body feight and muscle mass would be a percentage, so I created a seconbdary axis for these,
you would want the fat and muscle mass to not overlap the weight, so as to read the chart better, so I set the max on the secondary scale to 1, even though the body fat and muscle mass should never go anywhere near these numbers



Option Explicit

Sub CreateChart()
Dim LastRow As Long
Dim sh As Worksheet

Set sh = ActiveSheet
LastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
Charts.Add
With ActiveChart

.ChartType = xlLineMarkers

.SetSourceData Source:=sh.Range("A1:D" & LastRow)
.Location Where:=xlLocationAsObject, Name:=sh.Name
End With

With ActiveChart

.SeriesCollection(3).AxisGroup = 2
.SeriesCollection(2).AxisGroup = 2
With .Axes(xlValue, xlSecondary)

.TickLabels.NumberFormat = "0.00%"
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnit = 0.2
End With
.PlotArea.ClearFormats
.Axes(xlValue).MajorGridlines.Border.ColorIndex = 15
.Legend.Position = xlBottom

With .SeriesCollection(1)

With .Border
.ColorIndex = 57
.Weight = xlThick
.LineStyle = xlContinuous
End With

.MarkerBackgroundColorIndex = xlAutomatic
.MarkerSize = 9
End With

With .SeriesCollection(2)

With .Border
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With

.MarkerBackgroundColorIndex = 3
.Smooth = False
.MarkerSize = 7
End With

With .SeriesCollection(3)

With .Border
.ColorIndex = 4
.Weight = xlMedium
.LineStyle = xlContinuous
End With

.MarkerBackgroundColorIndex = 4
.MarkerSize = 7
End With
End With
End Sub

[choubix]

sorry for the late reply.

thanks for the code!
trying it today! :)

[Bob Phillips]

I thought you had forgotten this one, and after I went trhe extra for you :-)

Let us know how it goes.