Excel macro for multiple series in a single scatter graph using 3 columns


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Excel macro for multiple series in a single scatter graph using 3 columns

  1. #1
    Join Date
    May 2006
    Posts
    2

    Excel macro for multiple series in a single scatter graph using 3 columns

    Hi. I am new to this forum and also new to macros to MS Excel. However, I do have a basic understanding of C++/VB codes and structures. So I really appreciate any help anyone can offer.

    I currently am working on writing a macro for Excel to open a text data file and using the the data to make a graph. I already finished writing the part for opening and importing the file. However I am having trouble do the graphing part.

    The data is in 3 columns. One is for x-axis (Column 1), one is for y-axis (Column 2) and one is for the IDs (Column 3). There are undefined number of IDs and the number entries under the same the IDs can vary, however they are all grouped together.

    For example, the three columns would look like something below (sorry, I don't know how to embed the file in...):

    Col1 (x)______Col2 (y)______Col3 (ID)
    25.05______218.24______244
    349.18______218.82______244
    399.66______218.64______244
    450.90______217.68______244
    800.62______195.84______244
    1200.61______160.37______244
    24.94______215.09______686
    50.91______214.91______686
    99.97______214.12______686
    150.45______213.16______686
    401.91______204.67______686
    450.16______202.09______686
    800.66______178.10______686
    1202.50______146.92______686
    24.98______218.56______351
    50.43______218.48______351
    99.97______218.24______351
    149.62______217.97______351

    ...and so on. There could be as few as one ID that only have one entry in the file or as many as 100 IDs with 100 entries under each of them. The part I cannot figure out is how to graph all the Column 1 vs Column 2 data with the same IDs (same values in Column 3) on the same graph.

    Thanks for any help that you guys can offer.

  2. #2
    Join Date
    May 2006
    Posts
    2
    Hey, thanks for all those who looked at my post. I have already gotten an answer from another forum.

    If anyone is interested, the reply is below:

    Andy Pope

    Posts: n/a
    Re: Excel macro for multiple series in a single scatter graph using3 columns
    Hi,

    This should get you started.
    Assumed your data is in A1:C19, including header row.

    Sub CreateChart()

    Dim lngRow As Long
    Dim lngStartRow As Long
    Dim objChart As Chart
    Dim objSeries As Series

    Set objChart = ActiveSheet.ChartObjects.Add( _
    100, 100, 400, 250).Chart
    objChart.ChartType = xlXYScatterLines

    lngStartRow = 2
    lngRow = 2
    With ActiveSheet
    lngStartRow = 2
    lngRow = 2
    Do While Len(.Cells(lngRow, 1).Value) > 0
    If .Cells(lngStartRow, 3) <> .Cells(lngRow, 3) Then
    ' deal with grouped ID
    Set objSeries = objChart.SeriesCollection.NewSeries
    objSeries.Name = .Cells(lngStartRow, 3).Value
    objSeries.XValues = _
    ..Range("A" & lngStartRow, "A" & lngRow - 1)
    objSeries.Values = _
    ..Range("B" & lngStartRow, "B" & lngRow - 1)
    lngStartRow = lngRow
    End If
    lngRow = lngRow + 1
    Loop
    Set objSeries = objChart.SeriesCollection.NewSeries
    objSeries.Name = .Cells(lngStartRow, 3).Value
    objSeries.XValues = .Range("A" & lngStartRow, "A" & lngRow - 1)
    objSeries.Values = .Range("B" & lngStartRow, "B" & lngRow - 1)
    lngStartRow = lngRow
    End With

    End Sub

    Cheers
    Andy

    trumptmast wrote:
    > ....Can anyone help me with this? Does anyone need more information?
    >
    >

    --

    Andy Pope, Microsoft MVP - Excel
    http://www.andypope.info
    Last edited by trumptmast; 05-15-2006 at 02:52 PM.

Similar Threads

  1. Using Word commands through an Excel macro
    By Pass0 in forum VB Classic
    Replies: 1
    Last Post: 10-24-2005, 02:26 PM
  2. Replies: 1
    Last Post: 10-23-2002, 11:35 AM
  3. How long before the next version??
    By _CAG in forum .NET
    Replies: 146
    Last Post: 08-12-2002, 11:40 PM
  4. Re: App Object (fixes)
    By Rob Teixeira in forum .NET
    Replies: 129
    Last Post: 06-06-2002, 06:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center