Discontiguous Chart Source

[Roland]

I want to create a chart with 1 serie which has data from 4 discontiguous
cells.
I defined 'b=6' but in practice it has a variable value, also counter has
variable values.
I tried two methods:
1/ with the union method to make a multirange selection
2/ by combining the cell addresses to a string.
None of these works. How do I solve this?

thanx in advance

Sub Macro1()
Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("Summery").Range("A21:I40"), PlotBy:=xlRows
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = "={""x1"",""x2"",""x3"",""x4""}"

Set temprange = Range(Cells(2, b))
For counter = 4 To 8 Step 2
Set temprange = Union(temprange, Range(Cells(counter, b)))
Next
.SeriesCollection(1).Values = temprange

'tempstring = Cells(2, b).Address
'For counter = 4 To 8 Step 2
' tempstring = tempstring & "," & Cells(counter, b).Address
'Next
'.SeriesCollection(1).Values = range(tempstring)

.SeriesCollection(1).Name = "=""test"""
.Location Where:=xlLocationAsObject, Name:="Summery"
End With
End Sub

 

[JLGWhiz]

The syntax for a chart series would be:

..SeriesCollection(1).XValues = Sheets(1).Range("B5, B8, B11, B35")

 

[Jon Peltier]

That doesn't work or a discontiguous range. You need to construct the string
which combines each area's worksheet-referenced address:

Sub ChartSeriesData()
Dim rngData As Range
Dim rArea As Range
Dim sAddress As String

Set rngData = ActiveSheet.Range("B5,B8,B11,B35")
sAddress = "="
For Each rArea In rngData.Areas
sAddress = sAddress & "'" & ActiveSheet.Name & "'!" & rArea.Address(, ,
xlR1C1) & ","
Next
sAddress = Left$(sAddress, Len(sAddress) - 1)
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).XValues = sAddress

End Sub

- Jon

 

[JLGWhiz]

Now I remember why I do most of my charts manually and just use VBA to call
them up. Thanks for the lesson Jon.

 

[Jon Peltier]

What's a pain is that you can refer to a range fine with this syntax, but it
doesn't work for a chart.

- Jon