Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dead chart
Hi,
I want to create a dead chart and use the following code, partly taken from http://www.vbaexpress.com/forum/arch...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 Whe=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dead chart
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. 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 Whe=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dead chart
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 "Claus Haslauer" wrote in message ... 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. 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 Whe=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dead spot in worksheet | Excel Discussion (Misc queries) | |||
Brain Dead | Excel Discussion (Misc queries) | |||
Brain Dead: Need help with ???? | Excel Worksheet Functions | |||
Is ADO dead? | Excel Programming |