scatter graph – source (Excel 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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:

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

I have attached a copy for your perusal

Reply | Quote

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

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

Steve

Reply | Quote