Dynamic Series problem in Dynamic Chart

[martin318is]

Hi,

I have made extensive use of the things I have learned here over the last week or so since I found the site. so THANK YOU!
I have a problem that I can't seem to get all the answers for though.

I have a worksheet that is based upon a single macro. The macro populates a base data sheet with data from a report. It then splits the data based upon a couple of criteria into a number of other sheets. (Each sheet is identical in terms of columns and content, with the exception that the data itself is based upon the criteria differences).

Everything up to here is fine and so I havent included it in the attachments.

THe macro then graphs the content of each of these sheets.

The trick comes because I want to define an unknown number of series for each chart based upon the content of one column. The data is already sorted and so I can do an
"if content of cell is not teh same as current series name then it is the start of the new series."

This can give me a seriesname and also I can set an rStart and rEnd for use in the XValues and Values.

BUT!
I am having a real problem trying to get the format right for the series.

My code looks like this:

Function Graph_Portfolio(runname As String)
' Graph_Portfolio Macro
' Macro recorded 23/06/2005 by mhouston
Dim r, rw, x, Index As Integer
Dim XValuesRange As Range
Dim ValueRange As Range
Dim ShSource As Worksheet
Set ShSource = ActiveSheet
Dim SeriesName As String
Dim rstart(), rend()
Sheets(runname).Select
gr = ActiveSheet.UsedRange.Rows.Count
'MyRange = (
' Clean up the table prior to graphing it
r = ActiveSheet.UsedRange.Rows.Count
' Strip out up to 4 blank non-data rows from the table and ensure that rowcount is updated..
For x = 1 To 4
r = ActiveSheet.UsedRange.Rows.Count
For rw = 3 To r
If ActiveSheet.Cells(rw, "G").Value = "" Then
ActiveSheet.Rows(rw).EntireRow.Delete
End If
Next rw
Next x
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=ShSource.Range("D6"), PlotBy:= _
xlRows
Index = 0
SeriesName = "None"
'r = ActiveSheet.UsedRange.Rows.Count
For rw = 3 To r
Sheets(runname).Select
If SeriesName <> "None" Then
If ActiveSheet.Cells(rw, "G").Value <> SeriesName Then
ActiveChart.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(Index).Name = SeriesName
XValueRange = (Range("E" & rstart & ":E" & rend))
ValueRange = (Range("G" & rstart & ":G" & rend))
ActiveChart.SeriesCollection(Index).XValues = " & ShSource.Name & .Range(XValueRange)"
ActiveChart.SeriesCollection(Index).Values = " & ShSource.Name & .Range(ValueRange)"
Index = Index + 1
rstart = Array(rw)
Else
rend(Index) = rw
End If
End If
Next rw
ActiveChart.Location Where:=xlLocationAsNewSheet
' Name the chart
chartname = runname + " PCE Chart"
ActiveChart.Name = chartname
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "BR " & runname & " - PCE Chart"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Method_Paths"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PCE"
End With
End Function

I have included a sample of one of these generated sheets.

I want to create a chart of "PCE" for "Bucket" for each series based upon "Series"

Any help would be awesome!

[martin318is]

Bouncing to the top because I'm desperate.


(Sorry, I know it isn't very polite. But I need to get this knocked over before tomorrow)

[Andy Pope]

Hi,

This is not perfect but it does at least produce a chart.

Function Graph_Portfolio(runname As String)
' Graph_Portfolio Macro
' Macro recorded 23/06/2005 by mhouston
Dim r, rw, x, Index As Integer
Dim XValuesRange As Range
Dim ValueRange As Range
Dim ShSource As Worksheet
Dim SeriesName As String
Dim rstart(), rend()
Dim lngStartRow As Long
Dim lngEndRow As Long
Dim objCht As Chart
Set ShSource = ActiveSheet
Sheets(runname).Select
    gr = ActiveSheet.UsedRange.Rows.Count
' Clean up the table prior to graphing it
    r = ActiveSheet.UsedRange.Rows.Count
    '   Strip out up to 4 blank non-data rows from the table and ensure that rowcount is updated..
    For x = 1 To 4
        r = ActiveSheet.UsedRange.Rows.Count
        For rw = 3 To r
If ActiveSheet.Cells(rw, "G").Value = "" Then
                ActiveSheet.Rows(rw).EntireRow.Delete
            End If
Next rw
    Next x
Set objCht = Charts.Add
    objCht.ChartType = xlLineMarkers
    objCht.SetSourceData Source:=ShSource.Range("D6"), PlotBy:=xlRows
SeriesName = ShSource.Cells(4, 1)
    lngStartRow = 4
For rw = 4 To r + 1
        If ShSource.Cells(rw, 1) <> SeriesName Then
            lngEndRow = rw - 1
            Index = Index + 1
            If Index > 1 Then objCht.SeriesCollection.NewSeries
            objCht.SeriesCollection(Index).Name = SeriesName
            Set XValuesRange = ShSource.Range("E" & lngStartRow & ":E" & lngEndRow)
            Set ValueRange = ShSource.Range("G" & lngStartRow & ":G" & lngEndRow)
            objCht.SeriesCollection(Index).XValues = XValuesRange
            objCht.SeriesCollection(Index).Values = ValueRange
            lngStartRow = rw
            SeriesName = ShSource.Cells(rw, 1)
        End If
    Next rw
'   Name the chart
    chartname = runname + " PCE Chart"
    objCht.Name = chartname
With objCht
        .HasTitle = True
        .ChartTitle.Characters.Text = "BR " & runname & " - PCE Chart"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Method_Paths"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PCE"
    End With
End Function

I think the ranges for the values and xvalues maybe mixed.
Also the x axis labels should only be set once not for each series.
If you still stuck maybe post an example of what the finished chart shoud look like.

[martin318is]

Thank you! That is excellent.

I only had to make a very minor change to modify the source for the Series Names. Not a problem.

The only problem with it is that the X-axis labels. The labels are partly series specific, and since they are only printed once, the label set for the first series is printed, regardless of whether it is the longest set of data or not.

I think I can sort this out though and will post the final version when I do.

Thanks again!
Martin

[martin318is]

After thinking about it using a fresh brain inserted only this morning, I realised taht I can get away with:

objCht.SeriesCollection(Index).XValues = Array(0, 7, 14, 30, 60, 90, 120, 150, 180, 270, 371, 546, 736, 1101, 1466, 1832, 2197, 2560, 2927, 3293, 3658, 4388, 5484, 7310, 9137, 10963, 36164)
            'objCht.Axes(xlCategory).TickLabels.Font = 8
            With objCht.Axes(xlCategory).TickLabels
                .Alignment = xlCenter
                .Offset = 100
                .ReadingOrder = xlContext
                .Orientation = -45
             End With

Because these are test results and the test set-up involves ensuring that data is gathered only for these values.

Thanks again!
Martin