Creating Graphs + VBA

[Sduduzo]

Hi All

I have a bunch of data that I need to graph. The data basically contains
statistics per person (Min,Max and AVG). I have this in 100 rows.

I need to create a bar chart per person with their stats in graph.

I found the code below to create the grpahs, but I cannot get it to show my
series labels correctly, they just show up as Series1, Series2, Series3,
where as I need it to show Min, Max, Avg :

Sub CreateBarCharts()
Dim ws As Worksheet, cel As Range

Set ws = ActiveSheet
With ws
For Each cel In .Range(.[A2], .Cells(.Rows.Count, "A").End(xlUp))
With Charts.Add
.ChartType = xlColumnClustered
.SetSourceData Source:=cel.Resize(1, 8), PlotBy:=xlRows
.Location Where:=xlLocationAsNewSheet
.HasTitle = True
.ChartTitle.Characters.Text = cel.Value
With .Axes(xlCategory, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = ws.[A1]
End With
End With
Next cel
End With

End Sub


Any help would be appreciated.

 

[OssieMac]

Not really sure that I understand your question correctly. I suspect that you
mean that you want the columns to be named. However, the following example
code might point you in the right direction.

Feel free to get back to me if not what you are after but might need a bit
more explanation.

Sub CreateBarCharts()
Dim ws As Worksheet, cel As Range

Set ws = ActiveSheet
With ws
For Each cel In .Range(.[A2], .Cells(.Rows.Count, "A").End(xlUp))
With Charts.Add
.ChartType = xlColumnClustered

'.SetSourceData Source:=cel.Resize(1, 8), PlotBy:=xlRows
'**********************************************
'Inserting column headers under columns where
'ws.Range("A1:H1") is the column headers
.SetSourceData Source:=Union(ws.Range("A1:H1"), _
cel.Resize(1, 8)), PlotBy:=xlRows
'**********************************************

.Location Where:=xlLocationAsNewSheet
.HasTitle = True
.ChartTitle.Characters.Text = cel.Value

'******************************************
'Options for coding Series Name"
'.SeriesCollection(1).Name = "=""Min"""
'.SeriesCollection(1).Name = "=" & Chr(34) & "Min" & Chr(34)
.SeriesCollection(1).Name = "=" & Chr(34) & ws.[B1] & Chr(34)
'*******************************************
With .Axes(xlCategory, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = ws.[A1]
End With
End With
Next cel
End With