Plot Columns With Chart Macro

  • hi..


    I have 2 columns og data, for example



    I want to plot them via a chart. But here is the problem..
    My code


    Code
    Charts.Add
        ActiveChart.ChartType = xlXYScatterSmooth
        NextRow = Worksheets(datasheet).Range("G65536").End(xlUp).Row
        With ActiveChart.SeriesCollection(1)
            .XValues = Worksheets(datasheet).Range("F1", "F" & NextRow)
            .YValues = Worksheets(datasheet).Range("G1", "G" & NextRow)
        End With


    Where the above data, is in the range specified.
    The name

    Code
    datasheet

    is the name of my current worksheet.
    The funny thing is that there comes an error in the YValues section. It says:
    Error 438
    Object doesn't support this property or method.


    I have also tried without the

    Code
    With


    And a small thing, how do you include the chart in a sheet, and not in a seperate chartsheet?


    thx

  • Re: Chart In Macro


    i think you left out the aprostophees


    Code
    With ActiveChart.SeriesCollection(1) 
        .XValues = Worksheets("datasheet").Range("F1", "F" & NextRow) 
        .YValues = Worksheets("datasheet").Range("G1", "G" & NextRow) 
    End With 
    
    
    'place in chartsheet
    ActiveChart.Location Where:=xlLocationAsNewSheet

    Cheers
    ___________
    Xlite :sheep:
    [st]All you need to learn VBA is an internet connection and Ozgrid.com[/st]

  • Re: Chart In Macro


    Hi There,


    I think you're syntax is ever so slightly wrong...
    For the YValues the property is actually just 'Values' i.e.


    Code
    Charts.Add 
    ActiveChart.ChartType = xlXYScatterSmooth
    NextRow = Worksheets(datasheet).Range("G65536").End(xlUp).Row 
    With ActiveChart.SeriesCollection(1) 
        .XValues = Worksheets(datasheet).Range("F1", "F" & NextRow) 
        .Values = Worksheets(datasheet).Range("G1", "G" & NextRow) 
    End With


    to set the location of the chart try;

    Code
    ActiveChart.Location Where:=xlLocationAsObject, Name:=datasheet


    Obviously you can change datasheet to whichever sheet you want the chart to appear on.


    Hope that helps,
    Ian

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!