Plot two series collections in a chart vith VBA
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Plot two series collections in a chart vith VBA

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Brampton
    Posts
    328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I intended to update a chart with a macro based on some tabular data. The code in VBA is:

    Dim rowNumber As Integer
    Windows("Book1.xls").Activate
    With Worksheets("Sheet1")
    .ChartObjects(1).Activate
    .ChartObjects(1).Chart.HasTitle = True
    ActiveChart.ChartTitle.Select
    Selection.Characters.Text = "MY CHART"
    .ChartObjects(1).Select
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(1).XValues = .Range(Cells(8, 1), _
    Cells(rowNumber, 1)).Address(ReferenceStyle:=xlR1C1)
    ActiveChart.SeriesCollection(1).Values = .Range(Cells(8, 3), _
    Cells(rowNumber, 3)).Address(ReferenceStyle:=xlR1C1)
    ActiveChart.SeriesCollection(2).XValues = .Range(Cells(8, 1), _
    Cells(rowNumber, 1)).Address(ReferenceStyle:=xlR1C1)
    ActiveChart.SeriesCollection(2).Values = .Range(Cells(8, 66), _
    Cells(rowNumber, 1)).Address(ReferenceStyle:=xlR1C1)
    End With

    But when the code reaches the line which assigns XValues to the SeriesCollection(1) I get a run-time error "Method 'Cells' of object '_Global' failed. Any idea how to modify the code?

    [ This Message was edited by: corni on 2002-03-05 18:44 ]

  2. #2
    New Member
    Join Date
    Feb 2002
    Location
    Dallas, Texas
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You need to assign a value to rowNumber before this will work.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Brampton
    Posts
    328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I assigned the value for rowNumber, I simply omitted this in what I posted. In the meantime, as so many times happen I realized what was the cause for the error. All the range objects necessary to provide the Values and XValues had to be set up before the For ... Next statement, because in the For statement at that time active objects are the charts, not the worksheet. Thank you anyway for reviewing my problem.

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am getting the same error, but have not been able to resolve it. Could you please post the code that remedied your porblem. Where did you and how did you set up the XValues and Values before the For..Next statement. Thanks.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Brampton
    Posts
    328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    The code below was used to update chart data with XValues from the range A8:A38, Values(series1) from C8:C38 and values(series2) from BN8:BN38. The only condition not to fail range object methods is to have some data in the existing series.

    Dim rowNumber As Integer, r(2) As Range
    Windows("Book2").Activate
    rowNumber = 38
    Set r(0) = Range(Cells(8, 1), Cells(rowNumber, 1))
    Set r(1) = Range(Cells(8, 3), Cells(rowNumber, 3))
    Set r(2) = Range(Cells(8, 66), Cells(rowNumber, 66))
    With Worksheets("Sheet1")
    .ChartObjects(1).Activate
    .ChartObjects(1).Chart.HasTitle = True
    ActiveChart.ChartTitle.Select
    Selection.Characters.Text = "MY CHART"
    .ChartObjects(1).Select
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(1).XValues = r(0)
    ActiveChart.SeriesCollection(1).Values = r(1)
    ActiveChart.SeriesCollection(2).XValues = r(0)
    ActiveChart.SeriesCollection(2).Values = r(2)
    End With

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com