PDA

View Full Version : Solved: naming chart series from a cell value



AJS
10-05-2005, 03:29 AM
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:

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

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
10-05-2005, 05:32 AM
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
10-05-2005, 06:31 AM
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
10-05-2005, 06:41 AM
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
10-05-2005, 05:52 PM
Hi Andy,

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

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

Cheers, Aaron