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

[vba]

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" & 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

[/vba]

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