Creating Multiple Scatter Charts

jschm · #1 06-25-2009, 10:59 AM

I have 3 columns of data: ID #, Distance, and Time. Each ID # has a set of Times and Distances
I want to create a separate graph for each different ID #, with Time on x-axis and distance on y-axis. Is there any way to do this without manually creating one graph at a time?

Could a Macro do it automatically for me?

Thanks!

For example:
ID# Distance Time
1 100 2.3
1 93 2.4
1 45 3.2
34 321 0.3
34 231 1.4
34 54 5.3
547 47 0.3
547 2 1.3

Would create 3 separate scatter plots (one for each unique ID#) with time on x-axis and distance on y-axis

Cyclops · #2 06-25-2009, 03:57 PM

Not sure if 2007 works the same for charts but this should works in 2003

Data must be selected before running macro. Much customization could be done with graph sizes, locations, colors, axis scales, etc.

Code:

Public Sub MakeGraphs()
Dim Item
Dim ChartRanges() As Range
Dim I As Long
Dim TempChart As ChartObject
Dim TempSeries As Series
Dim Cleft As Double
Dim Ctop As Double
Dim Cwidth As Double
Dim Cheight As Double
Dim Cspacing As Double

Cleft = 200
Ctop = 20
Cwidth = 200
Cheight = 200
Cspacing = 10




For Each Item In Selection.Resize(, 1)

    If IsNumeric(Item.Value) Then 'ignores the title column if it's selected
    
        If Not Item.Value = Item.Offset(-1).Value Then
            ReDim Preserve ChartRanges(I)
            Set ChartRanges(I) = Item
            I = I + 1
        Else
            
            Set ChartRanges(I - 1) = Union(ChartRanges(I - 1), Item)
        End If
    End If
Next


For Each Item In ChartRanges()
    Set TempChart = ActiveSheet.ChartObjects.Add(Cleft, Ctop, Cwidth, Cheight)
    TempChart.Chart.ChartType = xlXYScatter
    Set TempSeries = TempChart.Chart.SeriesCollection.NewSeries
    TempSeries.Name = Item.Resize(1, 1).Value
    TempSeries.Values = Item.Offset(, 1)
    TempSeries.XValues = Item.Offset(, 2)
    Ctop = Ctop + Cheight + Cspacing
Next


End Sub

Welcome to the Excel Forum

Forum Statistics