Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    scatter graph - source (Excel 2003)

    I need to make 50 scattergraphs like the one attached on M7. Some of the datasets are on D1. My problem is that I have to select each series and x/y value manually one at a time in the Source Data input screen. This is laborious. I've entered it several ways into the "Data Range" tab by selecting the whole range in the column input box but it doesn't come out right so i have to go back and enter them one at a time. Is there another way to do these or is there a macro I could use that will do each chart based on what I select. Hopefully there is a faster way to do this. Thank you.
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: scatter graph - source (Excel 2003)

    Hi there

    I think this will do what you want but I can tidy it up if you need.

    I have transposed all your data into 3 rows B2:AH3. The code I have written will count the used rows so this will avry I imagine. Now the code:

    <pre>Sub Scatter()

    Dim ColCount As Integer
    Dim Tag As String
    Dim XCoord As String
    Dim YCoord As String
    Dim i As Integer

    Worksheets("D1").Select
    Range("B2").Select
    ColCount = Worksheets("D1").UsedRange.Columns.Count
    Charts.Add
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Sheets("D1").Range("B2:AH2"), PlotBy:= _
    xlColumns
    For i = 1 To ColCount - 1
    XCoord = "='D1'!R3C" & i + 1
    YCoord = "='D1'!R4C" & i + 1
    Tag = "='D1'!R2C" & i + 1
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(i).XValues = YCoord
    ActiveChart.SeriesCollection(i).Values = XCoord
    ActiveChart.SeriesCollection(i).Name = Tag


    Next i


    End Sub

    </pre>



    I have attached a copy for your perusal
    Jerry

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: scatter graph - source (Excel 2003)

    Here is my version. I also chose to format it for you as well. Just select the range and run the code...

    <pre>Option Explicit
    Sub CreateChart()
    Dim cht As Chart
    Dim rng As Range
    Dim iCols As Integer
    Dim iCol As Integer
    Dim sName As String
    Dim ser As Series
    Dim sForm As String

    Set rng = Selection

    iCols = rng.Columns.Count
    Set rng = rng.Resize(3, iCols)
    sName = "'" & rng.Parent.Name & "'!"

    Set cht = Charts.Add
    With cht
    .ChartType = xlXYScatter
    For Each ser In .SeriesCollection
    ser.Delete
    Next
    For iCol = 1 To iCols
    .SeriesCollection.NewSeries
    sForm = "=Series(" & _
    sName & rng.Cells(1, iCol).Address & ", " & _
    sName & rng.Cells(2, iCol).Address & ", " & _
    sName & rng.Cells(3, iCol).Address & "," & iCol & ")"
    .SeriesCollection(iCol).Formula = sForm
    .SeriesCollection(iCol).ApplyDataLabels _
    ShowSeriesName:=True
    Next
    With .Axes(xlCategory)
    .MinimumScale = 0
    .MaximumScale = 5
    .MajorTickMark = xlNone
    .MinorTickMark = xlNone
    .TickLabelPosition = xlNone
    End With
    With .Axes(xlValue)
    .MinimumScale = 0
    .MaximumScale = 5
    .MajorTickMark = xlNone
    .MinorTickMark = xlNone
    .TickLabelPosition = xlNone
    .MajorGridlines.Delete
    End With
    With .PlotArea
    .Interior.ColorIndex = xlNone
    .Border.ColorIndex = xlNone
    End With
    .Legend.Clear
    End With
    End Sub</pre>


    Steve

Posting Permissions

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