+ Reply to Thread
Results 1 to 2 of 2

Plot Using Setsourcedata Method with Multiple but Variable Number of Series

Hybrid View

lp3eb Plot Using Setsourcedata... 03-31-2014, 11:42 AM
lp3eb Re: Plot Using Setsourcedata... 03-31-2014, 12:46 PM
  1. #1
    Registered User
    Join Date
    03-14-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    9

    Plot Using Setsourcedata Method with Multiple but Variable Number of Series

    I am trying to plot multiple series on the same plot using the setsourcedata method. The tricky part is that with this spreadsheet, the number of series is variable. In other words, one day there might be two series that I need to plot, and the next day there might be five, so the exact locations of the series at which data must be pulled from changes depending on the number of series that must be plotted. Right now I am using the integer h to iterate the series, however it only plots the last series in the group. Can this be done with the setsourcedata method? Is there another way to accomplish what I am trying to do? Here is my code so far:

    Sub AddChart()
    
    Dim chrt As Chart
    Dim h As Integer
    Dim o As Integer
    Dim rng_o As Range
    
    h = 0
    o = Sheets("Enter Data").Range("A3")
    
    If Sheets("Plot").ChartObjects.Count > 0 Then
        Sheets("Plot").ChartObjects.Delete
    End If
    
    'Adds chart and defines position
    Set chrt = Sheets("Plot").Shapes.AddChart(xlXYScatterLines, Cells(4, 8).Left, Cells(4, 8).Top).Chart
    
    With chrt
        
        'Source Data
        For Each rng_o In Range(Cells(1, 3), Cells(o, 3))
            .SetSourceData Source:=Range(Cells(5, 2 * h + 1), Cells(5, 2 * h + 2).End(xlDown)), PlotBy:=xlColumns
            h = h + 1
        Next
        
        'Legend
        .HasLegend = False
        
        'Chart Title
        .HasTitle = True
        .ChartTitle.Text = Cells(2, 1)
        
        'X Axis
        If Not Cells(2, 3) = 0 Then
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = Cells(2, 3)
        End If
        
        'Y Axis
        If Not Cells(2, 4) = 0 Then
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Cells(2, 4)
        End If
    End With
        
    End Sub

  2. #2
    Registered User
    Join Date
    03-14-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Plot Using Setsourcedata Method with Multiple but Variable Number of Series

    Now I'm trying the seriescollection approach, but I keep getting application defined or object defined errors. Am I close?

    Sub AddChart()
    
    Dim chrt As Chart
    Dim h As Integer
    Dim o As Integer
    Dim rng_o As Range
    
    h = 0
    o = Sheets("Enter Data").Range("A3")
    
    If Sheets("Plot").ChartObjects.Count > 0 Then
        Sheets("Plot").ChartObjects.Delete
    End If
    
    'Adds chart and defines position
    Set chrt = Sheets("Plot").Shapes.AddChart(xlXYScatterLines, Cells(4, 8).Left, Cells(4, 8).Top).Chart
    
    With chrt
        
        'Source Data
        For Each rng_o In Range(Cells(1, 3), Cells(o, 3))
            .SeriesCollection.NewSeries 
            .SeriesCollection.XValues = Range(Cells(5, 2 * h + 1).End(xlDown))
            .SeriesCollection.Values = Range(Cells(5, 2 * h + 2).End(xlDown))
            h = h + 1
        Next
        
        'Legend
        .HasLegend = False
        
        'Chart Title
        .HasTitle = True
        .ChartTitle.Text = Cells(2, 1)
        
        'X Axis
        If Not Cells(2, 3) = 0 Then
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = Cells(2, 3)
        End If
        
        'Y Axis
        If Not Cells(2, 4) = 0 Then
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Cells(2, 4)
        End If
    End With
        
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Scatter plot multiple series
    By mitchurugie in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-17-2013, 11:59 AM
  2. [SOLVED] Referring to a Variable Range of Columns for the SetSourceData Method
    By DobbsHead in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-29-2012, 03:56 PM
  3. multiple series on a scatter plot
    By RobynB in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-17-2011, 07:51 PM
  4. plot multiple time series
    By Moohwan in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-27-2006, 07:40 AM
  5. [SOLVED] Series.Values vs Chart.SetSourceData
    By Hans in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-20-2006, 07:25 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1