adding column clustered chart

**buckfran** · 08-12-2009, 12:33 PM

Hi, I'm trying to add a column clustered chart and I'm not sure how to define the names, values and x category labels for my different series. I posted what code I do have but I am just trying to follow the Peltier website. I like how the ranges are easy to define in his method. I will post the link to that below. I think that the Peltier code is intended for a scatter line plot. I obviously need to create a column clustered chart instead. Any help you can give to put me on the right track would be greatly appreciated.

Thanks

http://peltiertech.com/Excel/ChartsH...html#VBAcharts

Code:

Private Sub CommandButton2_Click()
    Set myChtObj = Worksheets("Individual Jobs").ChartObjects.Add _
        (Left:=12, Width:=370, Top:=2042, Height:=239)
    myChtObj.Chart.ChartType = xlColumnClustered
    With ActiveChart.SeriesCollection.NewSeries
        .Name = Worksheets("Graph Reference").Range("AA4")
        .Values = Worksheets("Graph Reference").Range("some range")
        .XValues = Worksheets("Graph Reference").Range("some range")
    End With

**Andy Pope** · 08-12-2009, 12:52 PM

it would help if you post a sample data file or defined the ranges you intend using.

What does the code currently produce?

**buckfran** · 08-12-2009, 07:45 PM

It does not currently produce anything. I am using the Peltier framework, but I don't think that it works for a clustered chart. I understand that the .name is the series name, but it is unclear to me what the .Values and .Xvalues are. The current error that I receive is "Object variable or with block variable not set".

If I were to use the chart creation tool provided with excel, I would have two series.

The name of series 1 would be in AA4 and the series would have the values in AA13 and AV13.

The name of series 2 would be in AB4 and the series would have the values in AB13 and AW13.

Both series would have the same Category (X) Axis labels.

**Andy Pope** · 08-13-2009, 06:18 AM

The error is because you create a chart object on the non active sheet and then rather than using the object reference you created you use the Activechart object. I would guess that Jon's original code did not create the chartobject on a different sheet to the data.

Code:

    Dim myChtObj As ChartObject
    
    Set myChtObj = Worksheets("Individual Jobs").ChartObjects.Add _
                    (Left:=12, Width:=370, Top:=10, Height:=239)
    myChtObj.Chart.ChartType = xlColumnClustered
    With myChtObj.Chart.SeriesCollection.NewSeries
        .Name = Worksheets("Graph Reference").Range("AA4")
        .Values = Worksheets("Graph Reference").Range("AA13:AV13")
        .XValues = Worksheets("Graph Reference").Range("AA12:AV12")
    End With
    With myChtObj.Chart.SeriesCollection.NewSeries
        .Name = Worksheets("Graph Reference").Range("AB4")
        .Values = Worksheets("Graph Reference").Range("AA14:AV14")
        .XValues = Worksheets("Graph Reference").Range("AA12:AV12")
    End With

The .Values property refers to the actual data being plotted against the value (Y) axis.
The .XValues property refers to the data used on the category axis. In a column chart this would be the axis labels.