How to select data for x-axis in macro plot

**elementalsurf7** · 07-01-2010, 08:42 AM

I tried recording a macro that generates a scatter plot using one column as the x-axis data, and several columns for y. The plots come out correctly when I record the macro, but when I apply it to new data, it plots it by index, rather than data points. For example:

Correct:
x...................y
1.4928............1394.23
2.3459............2492.42
3.9846............142.42

Macro:
x.................y1.................y2
1..............1.4928...........1394.23
2..............2.3459...........2492.42
3..............3.9846............142.42

How can I specify the x-axis for the macro, when I already tried to do it during the recording? I found I can do it by hand, but it is tedious; I have to select each series at a time and re-select the x-axis data, making a macro obsolete. Here is my code for one of the plots, and I have attached the graphs.

Range("A33:A60,B33:E60").Select
    Range("B33").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range( _
        "Sheet1!$A$33:$A$60,Sheet1!$B$33:$E$60")
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="A-B"
    Sheets("Sheet1").Select

The workbook originally has a ton of data, but is reduced significantly when the macro is run. I've also attached what the graph looked like when I recorded the macro, and what it should look like. Thank you.

**romperstomper** · 07-01-2010, 09:01 AM

A sample workbook would be more useful. Also, I assume you are actually using 2007 (rather than the 2003 shown in your profile) since that code wouldn't work in 2003?

**elementalsurf7** · 07-01-2010, 01:54 PM

I figured out how to get all of them in order using the longer code, and setting the range to:

    ActiveSheet.Range("A33:E60").Select

Sub EmbeddedChartFromScratch()
    Dim myChtObj As ChartObject
    Dim rngChtData As Range
    Dim rngChtXVal As Range
    Dim iColumn As Long

    ' make sure a range is selected
    If TypeName(Selection) <> "Range" Then Exit Sub

    ' define chart data
    Set rngChtData = Selection

    ' define chart's X values
    With rngChtData
        Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
    End With

    ' add the chart
    Set myChtObj = ActiveSheet.ChartObjects.Add _
        (Left:=250, Width:=375, Top:=75, Height:=225)
    With myChtObj.Chart

        ' make an XY chart
        .ChartType = xlXYScatterLines

        ' remove extra series
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop

        ' add series from selected range, column by column
        For iColumn = 2 To rngChtData.Columns.Count
            With .SeriesCollection.NewSeries
                .Values = rngChtXVal.Offset(, iCOlumn - 1)
                .XValues = rngChtXVal
                .Name = rngChtData(1, iColumn)
            End With
        Next

    End With

End Sub


'Read more: Quick Excel Chart VBA Examples http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html#addseries#ixzz0sSAtZBYG