Creating Multiple Charts on one worksheet

**Turion** · 11-24-2009, 08:53 AM

Hi

I have a bit of a problem I created a code that produce a chart from a range. Could you please help and let me know how can I loop through ranges to create multiplecharts on one workseet. Data is in separate sheet in column 2 to the right with none empty cells. I want to loop through rows 2 to 29 to create seprate chart per row.
Code below work for only one row,when I change the row number I got the error message: Method " 'Columns' of object'_Global' failed "

Code:

Sub LineColumnChart()
    Dim rng1 As Worksheet
    Dim r As Integer, c As Integer, x As Integer
    Dim rngXVal As Range
    Dim rngVal As Range

    Application.ScreenUpdating = False
    Set rng1 = Worksheets("Data")
            
    For r = 2 To 2
    Set rngXVal = rng1.Range(rng1.Cells(r, 2), rng1.Cells(r, Columns(2).Count).End(xlToRight))
    x = (r - 1) * (-1)
    
    With Worksheets("SingleChart").ChartObjects.Add(0, 0, 850, 300).Chart

    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = rngXVal.Offset(0, -1)
    .SeriesCollection(1).XValues = rngXVal.Offset(x, 0)
    .SeriesCollection(1).Values = rngXVal
    .SeriesCollection.NewSeries
    .SeriesCollection(2).Values = rngXVal.Offset(29, 0)
   
    .Location Where:=xlLocationAsObject, Name:="SingleChart"
    .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
    .Axes(xlValue).HasMajorGridlines = True
    .Axes(xlValue).MajorGridlines.Border.ColorIndex = 57
    .Axes(xlValue).MajorGridlines.Border.Weight = xlHairline
    .Axes(xlValue).MajorGridlines.Border.LineStyle = xlDot
    .HasTitle = True
    .ChartTitle.Text = "Accuracy and completeness of data to be maintanined"
    .HasLegend = False
    .SeriesCollection(1).Border.LineStyle = xlDot
    .SeriesCollection(1).Interior.ColorIndex = 12
    .PlotArea.Interior.ColorIndex = 2
    .PlotArea.Interior.PatternColorIndex = 1
    .SeriesCollection(1).Trendlines.Add
    .SeriesCollection(1).Trendlines(1).Type = xlLogarithmic
    End With
    Next r
    
    Application.ScreenUpdating = True
End Sub

**Andy Pope** · 11-24-2009, 09:50 AM

Can you post small example, say 3 rows of data.

Manually create the charts so we can see how the final output should look, assuming you do not want all the charts in the top left corner.

**Turion** · 11-24-2009, 02:31 PM

Thank you for looking into it. I added three lines of data plus two for additional series.

**Andy Pope** · 11-25-2009, 06:09 AM

Your attachment does not contain any charts so I have no idea how to present the data.

**Turion** · 11-25-2009, 06:27 AM

Hi sory about that, it doesnt matter much, charts might be layout one below the other.

**Andy Pope** · 11-25-2009, 06:31 AM

So how does Series1 and Series2 fit into the chart??

**Turion** · 11-25-2009, 10:14 AM

I hope I did explain this in a right way, I have macro that creates one chart from the first row of data I dont know how to loop to create 28 charts one chart per row. The second series is offset to get data from 28 rows below first one. Can you direct me how to loop through rows?

**Andy Pope** · 11-25-2009, 11:06 AM

Try this.

Code:

Sub LineColumnChart()
    Dim rngLabels As Range
    Dim rngRecs As Range
    Dim rngCht As Range
    Dim objCht As Chart
    Dim sngLeft As Single
    Dim sngTop As Single
    Dim sngWidth As Single
    Dim sngHeight As Single
    
    On Error Resume Next
    
    With Worksheets("Data")
        Set rngLabels = .Range("B1", .Cells(1, .Columns.Count).End(xlToLeft))
        Set rngRecs = .Range("A2", .Range("A2").End(xlDown))
    End With
    
    sngLeft = 0
    sngTop = 0
    sngWidth = 850
    sngHeight = 300
    
    For Each rngCht In rngRecs
        Set objCht = Worksheets("SingleChart").ChartObjects.Add(0, sngTop, sngWidth, sngHeight).Chart
        With objCht
            ' remove any series automatically added
            Do While .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            Loop
            
            ' column series
            With .SeriesCollection.NewSeries
                .Name = rngCht
                .XValues = rngLabels
                .Values = rngCht.Offset(0, 1).Resize(1, rngLabels.Columns.Count)
                .ChartType = xlColumnClustered
                .Border.LineStyle = xlDot
                .Interior.ColorIndex = 12
                With .Trendlines.Add
                    .Type = xlLogarithmic
                End With
            End With
            
            ' line series
            With .SeriesCollection.NewSeries
                .Name = rngCht
                .XValues = rngLabels
                .Values = rngCht.Offset(29, 1).Resize(1, rngLabels.Columns.Count)
                .ChartType = xlLineMarkers
            End With
            
            .Axes(xlValue).HasMajorGridlines = True
            .Axes(xlValue).MajorGridlines.Border.ColorIndex = 57
            .Axes(xlValue).MajorGridlines.Border.Weight = xlHairline
            .Axes(xlValue).MajorGridlines.Border.LineStyle = xlDot
            .HasTitle = True
            .ChartTitle.Text = rngCht.Value
            .HasLegend = False
            .PlotArea.Interior.ColorIndex = 2
            .PlotArea.Interior.PatternColorIndex = 1
        End With
            
        sngTop = sngTop + sngHeight
    Next
    
End Sub

I had to add error resume next as some series do not have enough valid points and complain at the trendline being added.

**Turion** · 11-25-2009, 03:17 PM

Thank you so much, this is exactly what wanted to achieve. It works perfectly.

**manny7** · 12-16-2009, 05:39 PM

Andy you are a champion. With a bit of modification to meet my own needs, your code was able to generate the graphs I needed at a click of a button. This will save me a lot of time in the future. Thanks very much!!

**dhuston** · 11-24-2010, 04:40 PM

Andy,
This code is fantastic. It is nearly perfect for my needs. I do have two questions about it.

1) I do not understand how rngCht.Value is assigned a value? Can you point me in some direction that I can do further research?

2) In my last column of data I have a rank value and I want to only create charts where the rank value is <= 10. I am guessing that I might be able to use offset but I am not exactly sure. Any ideas?

Thanks
Dan

**Andy Pope** · 11-25-2010, 05:50 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Post a fresh question with reference and I will address your questions there.