CODEWITHSUNDEEP
excelvbagraphcharts
blaaat
blaaat

Reputation: 21

Creating an Excel macro for multiple graphs with the same x-values but different y-values

I'm trying to automate the creation of graphs in Excel by means of a macro. Basically I've got a lot of data (41 columns, starting from column C to column AQ) each one containing another 'name'. In one column there are two series in two different row sections (one from row 3 to 8, one from row 12 to 17).

I want one graph per column, so in total I'll have 41 graphs. Each graph contains the two series. In column B are the values on the X axis, and this is for every graph the same.

The purpose is to create a for next loop with the y-axis values, and have them all created at once.

I've tried coding the first part (just creating one graph with on x-axis the values from B3:B8 and on y-axis the values from C3:C8, and the second series just a few rows down.

I get an error and I don't understand why, I've tried different approaches but it never works. The error situates in this line: ActiveChart.SeriesCollection(1).Values = Range(Cells(3, 3), Cells(8, 3)).Value

Does anyone have any ideas what may be wrong or how to tackle this problem?

Sub Macro5()

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "='Blad1'!$A$1"
    ActiveChart.SeriesCollection(1).XValues = "='Blad1'!$B$3:$B$8"
    ActiveChart.SeriesCollection(1).Values = Range(Cells(3, 3), Cells(8, 3)).Value
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "='Blad1'!$A$10"
    ActiveChart.SeriesCollection(2).XValues = "='Blad1'!$B$12:$B$17"
    ActiveChart.SeriesCollection(2).Values = Range(Cells(12, 3), Cells(17, 3)).Value
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    Selection.Delete
    ActiveChart.SeriesCollection(1).Trendlines.Add
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    Selection.DisplayEquation = True
    Selection.DisplayRSquared = True    
    ActiveChart.SeriesCollection(2).Trendlines.Add
    ActiveChart.SeriesCollection(2).Trendlines(1).Select
    Selection.DisplayEquation = True
    Selection.DisplayRSquared = True

End Sub

Upvotes: 0

Views: 13804

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6063

Range isn't qualified, and the array you convert it to using .Values is probably not understood by Excel.

Change 

ActiveChart.SeriesCollection(1).Values = Range(Cells(3, 3), Cells(8, 3)).Value

to

ActiveChart.SeriesCollection(1).Values = Worksheets("Blad1").Range(Cells(3, 3), Cells(8, 3))

or to

ActiveChart.SeriesCollection(1).Values = "='Blad1'!$C$3:$C$8"

Upvotes: 0

bf2020
bf2020

Reputation: 732

In excel 2007, SeriesCollection.Add() expects a range.

Upvotes: 0

Related Questions