Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Generating Graph Excel 2k3 1

Status
Not open for further replies.

Asspin

Technical User
Jan 17, 2005
155
US
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!

Code:
    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
 



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
Code:
    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,

[glasses] [red][/red]
[tongue]
 
Thanks Skip, I will give it a try!

Dan
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top