dead chart

[Claus Haslauer]

Hi,
I want to create a dead chart and use the following code, partly taken
from http://www.vbaexpress.com/forum/archive/index.php/t-848.html.

In each cell of A2:B45 is
=RAND()
When I execute it, I get the error
Run-time error '1004': Unable to set the XValues property to the Seriees
class

When I put ordered numbers from 1 to 44 in A2:A45 and B2:B45, it works.

Can anybody explain why?
And make it work for unordered data (my real data is not =Rand(), but
measurements).

Thanks,
Claus

Here's the code:

Private Sub CommandButton1_Click()
Dim intSeries As Integer
Dim objChart As ChartObject
'
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("F7")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = Range("A2:A45")
ActiveChart.SeriesCollection(1).Values = Range("B2:B45")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

For Each objChart In ActiveSheet.ChartObjects
With objChart.Chart
For intSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(intSeries)
.XValues = .XValues
.Values = .Values
.Name = .Name
End With
Next
End With
Next

End Sub

 

[Claus Haslauer]

Sorry for my tone.
That was written in a hurry.
I meant to ask if somebody could provide some help to make it work for
unordered data.
Sorry again.

 

[Peter T]

When you convert the range source to values you make a long series formula.
With the set of values that works, select the series and look in the formula
bar.

The limit is an absolute maximum of 1024 overall formula length but will be
less than that. No doubt your rand formula produces more than that with all
those cells.

As you asked so nicely I have an addin that should solve your problem.
Converts source range to named arrays of values and overcomes the 1024
limit.

- Delink chart data from cells, incl X1/X2 & Y values, titles, data labels.
- Dump all named arrays in the workbook produced by the addin to a sheet
- Re-link all chart data to choice of new cell range

Apart from your objective can be used for removing links to another
workbook, move the source into same workbook (or move source within the same
wb), or as a "dead" chart with no links of any kind other than to "names".

Regards,
Peter T
pmbthornton gmail com