Generating Graph Excel 2k3 1

[Asspin]

Ok, I thought I had this code working, but apparently not. I get unable to set series class of whatever variable I am trying to set with everything after I add the 2 NewSeries. Any help would be great!

    Dim strValue1 As String, strValue2 As String, strTitle As String, strYaxis As String, iList As Integer, iList2 As Integer
    Application.ScreenUpdating = False
    iList = comGraph.ListIndex + 2
    iList2 = iList + 12
    strTitle = comGraph.Value & " vs. CSA Averages"
    strYaxis = comGraph.Value
    strValue1 = "=Reports!R6C" & iList & ":R17C" & iList
    If Reports.Range("B6").Value = 1 Then
        strValue2 = "=Variables!R31C" & iList2 & ":42C" & iList2
    Else
        strValue2 = "=Variables!R18C" & iList2 & ":R29C" & iList2
    End If
    Call ThisWorkbook.DeleteCharts
    Charts.Add
    With ActiveChart
        .ChartType = xlLine
        .Location Where:=xlLocationAsNewSheet
        .Name = "Chart"
        .SeriesCollection.NewSeries
        .SeriesCollection.NewSeries
        .SeriesCollection(1).XValues = "=Reports!R6C1:R17C1"
        .SeriesCollection(1).Values = strValue1
        .SeriesCollection(1).Name = Home.comMain3.Value
        .SeriesCollection(1).Smooth = True
        .SeriesCollection(1).Border.Weight = xlMedium
        .SeriesCollection(1).Border.ColorIndex = 57
        .SeriesCollection(2).Values = strValue2
        .SeriesCollection(2).Name = "CSA Average"
        .SeriesCollection(2).Smooth = True
        .SeriesCollection(2).Border.Weight = xlMedium
        .SeriesCollection(2).Border.ColorIndex = 3
        .HasTitle = True
        .ChartTitle.Characters.Text = strTitle
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = strYaxis
        .PlotArea.Interior.ColorIndex = 19
        .Deselect
    End With

Dan

 

[SkipVought]

Hi,

Here's the list...

1. You do not have Report defined as a Sheet Object. I used Sheets{"Report")

2. You are missing the ROW indicator in the range assignment

    If Sheets("Reports").Range("B6").Value = 1 Then
        strValue2 = "=Variables!R31C" & iList2 & ":[b]R[/b]42C" & iList2

I personally DESPISE the R1C1 format. I'd be using the Range & Cells method instead. In fact, I most often use Named Ranges.



Skip,

_[red][/red]

 

[Asspin]

Thanks Skip, I will give it a try!

Dan