+ Reply to Thread
Results 1 to 4 of 4

Creating a chart with multiple data-sources that have variable ranges ( in VBA)

Hybrid View

  1. #1
    Registered User
    Join Date
    07-09-2013
    Location
    Brussels, Belgium
    MS-Off Ver
    Excel 2007
    Posts
    15

    Creating a chart with multiple data-sources that have variable ranges ( in VBA)

    Hello there, I have some issues creating a chart.


    Context:
    I have to create a milestone slip chart (time-time chart). In order to achieve this I have:

    - one column with the date of every day on a certain number of years (represents my X axis)
    Content in this column goes from cell (3,3) to cell (i -1 ,3)

    - 9 columns of values (one for each milestone). The cells in these columns have either a value or no value ( this value is a date and is linked to a date on the x axis) (column index = 5,7,9... = 2 + k * 2 with k from 1 to 8)

    I need to plot a line for each milestone.

    here is my code:


    
    Set sh = ActiveWorkbook.Worksheets("Milestones")
    Set chrt = sh.Shapes.AddChart.Chart
    
    For k = 1 To 9
     With Sheets("Milestones")
     chrt.SeriesCollection.NewSeries
        chrt.SeriesCollection(" & k & ").XValues = _
            .Range(Cells(3, 3), Cells(i-1, 3))
        chrt.SeriesCollection(" & k & ").Values = _
            .Range(Cells(3, k*2+2), Cells(i-1, k*2+2))
    End With
    next k
    but it shows a run-time error 1004 ( application-defined or object-defined error) at this part of code
    chrt.SeriesCollection(" & k & ").XValues = _
            .Range(Cells(3, 3), Cells(i-1, 3))
    I have looked for hours on the net to find a solution and tried different syntax but nothing seems to work due to the variables i and k

    Thanks in advance

  2. #2
    Registered User
    Join Date
    07-09-2013
    Location
    Brussels, Belgium
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Creating a chart with multiple data-sources that have variable ranges ( in VBA)

    Anyone?... Please?

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,659

    Re: Creating a chart with multiple data-sources that have variable ranges ( in VBA)

    This syntax isn't correct.
    chrt.SeriesCollection(" & k & ").XValues

    Try this...
    chrt.SeriesCollection(k).XValues

    Or better yet.
        Set sh = ActiveWorkbook.Worksheets("Milestones")
        With sh.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225).Chart
            .ChartType = xlXYScatterLines
            For k = 1 To 9
                With .SeriesCollection.NewSeries
                    .Name = sh.Cells(2, k * 2 + 2)
                    .XValues = sh.Range(sh.Cells(3, 3), sh.Cells(i - 1, 3))
                    .Values = sh.Range(sh.Cells(3, k * 2 + 2), sh.Cells(i - 1, k * 2 + 2))
                End With
            Next k
        End With
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    07-09-2013
    Location
    Brussels, Belgium
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Creating a chart with multiple data-sources that have variable ranges ( in VBA)

    Thanks a lot for your time and answer, I'll try this tomorrow morning at work and tell you my results!

+ 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. Creating a report with text data from multiple data sources
    By radionut in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-25-2013, 10:17 AM
  2. Return sum of multiple variable data sources
    By MjRmatt in forum Excel General
    Replies: 2
    Last Post: 10-12-2012, 04:02 PM
  3. [SOLVED] Excel 2007 : Creating a pivot table with multiple data sources
    By tiggynook in forum Excel General
    Replies: 3
    Last Post: 05-30-2012, 01:56 PM
  4. creating a chart with multiple data sources
    By ravihotwok in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-28-2010, 07:59 AM
  5. Creating multiple charts with variable ranges
    By jcfryman in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 10-31-2009, 01:11 PM

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