Excel 2007 : SourceData for chart

[lilahh]

Hello,

I'm trying to add a chart using vba. I want P2:P603 to be the x values and L2:L603 to be the y values.

This is what I wrote:

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("P2:P603,L2:L603")

What am i doing wrong?

Thank you

[tlafferty]

I'd be glad to take a look, but before committing to that, I've got to ask you to abide by forum rules. Please enclose your post in code brackets so it will display correctly. Also, please consider posting a sample workbook with the complete code so it can be tested in the environment in which it will be run.

Regards,
Tom

[lilahh]

This is the enrite code, hope this is what you ment...

Sub hvgf()

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("P2:P603,L2:L603")


End Sub

[tlafferty]

Thanks for at least attempting to get at what I was suggesting. Sorry if it wasn't clearer. Check this link and scroll down to rule # 3 to see what I mean by code tags.
http://www.excelforum.com/forum-rule...rum-rules.html

In the meantime, can you upload a sample book and describe what the problems are that you are experiencing? I ran your code against a blank book and didn't encounter errors, so I'm assuming it's some other problem you're running into.

Let me know.

Tom

[lilahh]

Sorry for the inconvenient, it's my first visit here
here is the code again:

Sub hvgf()

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("P2:P603,L2:L603")


End Sub

The problem is that I want to display a chart of L2:L603 values as a function of P2:P603 values but instead, I get two series on the same chart...

Thank you very much

[teylyn]

try this, straight from the macro recorder:

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=Sheet1!$C$2"
    ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$B$3:$B$7"
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!$C$3:$C$7"

adjust ranges to suit.

cheers,

[lilahh]

Thank you so much!!!

Can I ask your help in order to define the x axis in log scale and also to determine the exact location of the chart in the sheet?


Thank you!!!!!!!!!!!!!!!!

[teylyn]

For Log scale:

    ActiveChart.Axes(xlCategory).ScaleType = xlLogarithmic

By the way: all that code comes straight from the macro recorder. You could easily do the same thing. Start the macro recorder, create a new, empty XY chart, define the data source, format the axes. Stop the macro recorder and tidy up the code.

[lilahh]

I will defently use it from now!!

Thank you so much, you were very helpful to me!!!

[lilahh]

One last thing...

I want to define the Y axis as logaritmic scale and not the X axis.

Even when I am using the recorder, the command appears with x and not with y.

Any help?

[teylyn]

My XL 2003 macro recorder recorded this:

'Y axis
    With ActiveChart.Axes(xlValue)
        .MinimumScaleIsAuto = True
        .MaximumScaleIsAuto = True
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLogarithmic
        .DisplayUnit = xlNone
    End With
'x axis
    ActiveChart.Axes(xlCategory).Select
    With ActiveChart.Axes(xlCategory)
        .MinimumScaleIsAuto = True
        .MaximumScaleIsAuto = True
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLogarithmic
        .DisplayUnit = xlNone
    End With