Solved: naming chart series from a cell value

[AJS]

Hi All,

I'm trying to set up a macro for creating x-y charts based on range selections made by the user. First I use input boxes to ask for the x-values range, as a column, then to ask for the y-values range(s), as one or multiple columns. I'd then like to be able to label the different series based on the value in the cell in the first row of the column containing the y values. Problem is, I'm not sure how to call this. This is the pertinent fragment of the code I'm currently using to add each new series of data:

[VBA] For i = 1 To YRange.Columns.Count

ActiveChart.SeriesCollection.NewSeries
.SeriesCollection(i).XValues = XRange
.SeriesCollection(i).Values = YRange.Columns(i)
.SeriesCollection(i).Name = ActiveSheet.Cells(1, YRange.Columns(i)).Value

Next i[/VBA]

where XRange and YRange are the users selected ranges. The end result is that no series get named - they all stay as "Series1", "Series2" etc...

Where am I going wrong?

Thanks, Aaron

[Andy Pope]

Hi,

Try this,

    For i = 1 To YRange.Columns.Count
        With ActiveChart
            .SeriesCollection.NewSeries
            With .SeriesCollection(.SeriesCollection.Count)
                .XValues = XRange
                .Values = YRange.Columns(i)
                .Name = ActiveSheet.Cells(1, YRange.Columns(i).Column).Value
            End With
        End With
    Next i

[AJS]

Hi Andy,

Thanks for that - that's solved half the problem. However, I'vr found that while the macro now works where the YRange is a continuous range, if YRange is made up of a number of non-adjacent columns only the first continuous block of columns is added as new series to the chart. Any idea how I can get it to work on non-continuous ranges as well?

Thanks, Aaron

[Andy Pope]

You need to take into account Areas then.

Sub x()

    Dim xrange As Range
    Dim yrange As Range
    Dim i
    Dim intArea As Integer
    
    Set xrange = Range("A2:A5")
    Set yrange = Range("B2:C5,E2:F5")
    
    For intArea = 1 To yrange.Areas.Count
        For i = 1 To yrange.Areas(intArea).Columns.Count
            With ActiveChart
                .SeriesCollection.NewSeries
                With .SeriesCollection(.SeriesCollection.Count)
                    .XValues = xrange
                    .Values = yrange.Columns(i)
                    .Name = ActiveSheet.Cells(1, yrange.Areas(intArea).Columns(i).Column).Value
                End With
            End With
        Next i
    Next intArea
End Sub

[AJS]

Hi Andy,

Thanks for that! I just had to make the following small modification:

.Values = YRange.Areas(intArea).Columns(i)

Cheers, Aaron