why won't excel allow me to add more data to a chart?

  • Thread starter Thread starter chrluce
  • Start date Start date
C

chrluce

For some of my data I skip days for collecting it so I use the shift key to
click on the days I do want in the chart. I have been doing this for awhile
but now that I'm obtaining more points it has simply stopped letting me add
data points. When I click on the one where it stops collecting it erases all
of the others I have clicked and makes a stop noise. I need to add more data
so why won't it let me??
 
Hi,

There is a finite limit on the length of the series formula, ~1024
characters.

So this example is for a series with 3 data points in cells A2:B4.
=SERIES(,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1)

If I use the SHIFT key and select each row individually the formula grows to
=SERIES(,(Sheet1!$A$2,Sheet1!$A$3,Sheet1!$A$4),(Sheet1!$B$2,Sheet1!$B$3,Sheet1!$B$4),1)

The simplest way around this is to create a contiguous range of chart data.

Cheers
Andy
 
SImilar problem here

Hi there,

I have used dynamic Arrays allocated to SeriesCollections values but the chart will not accept more than 256 values....I'm kinda stuck here, 2 days trying to get around this... Here is my VBA code, I use Excel 2003 and 2007, on XP Home Sp3. I checked and the csv file I load to the Arrays works fine. It seems the number of characters that can be writen in the field for Series Data is limited... Since the array is written "={0,3.0,5.0,01.0,07....etc} and I have more than 13000 values....it won't work!!

Thanks for the help!

______________________________________________________________

Public StkLastRow, StkLastColumn As Long
Public DIR As String
Public STOCK As String
Public CsvStockTable() As Variant


Sub ADD_Graphique()

Dim ArClose(), ArVolume(), ArOpen(), ArHigh(), ArLow(), ArAdjClose() As Double
Dim SVolume, SClose, SOpen, SHigh, SLow, SAdj As Variant
Dim ArDate() As Variant

Dim p As Variant
Dim i, Index As Integer

DIR = ActiveWorkbook.Path & "\"

STOCK = "SSS.V"

Load_CSV

'Nbr_Jours = 25

NRows = UBound(CsvStockTable, 1)
NColumns = UBound(CsvStockTable, 2)

If IsEmpty(Nbr_Jours) Then Nbr_Jours = NRows - 1
i = NRows - Nbr_Jours


Index = 0
ArrayDim = Nbr_Jours - 1

ReDim ArDate(ArrayDim)
ReDim ArClose(ArrayDim)
ReDim ArVolume(ArrayDim)
'ReDim ArOpen(ArrayDim)
'ReDim ArLow(ArrayDim)
'ReDim ArHigh(ArrayDim)
'ReDim ArAdjClose(ArrayDim)

Do
ArDate(Index) = CsvStockTable(i, 0)
'ArOpen(Index) = CsvStockTable(i, 1) * 100 / 100 ' I can't get the decimals to appear in the values of the graphic unless I x100/100??
'ArHigh(Index) = CsvStockTable(i, 2) * 100 / 100
'ArLow(Index) = CsvStockTable(i, 3) * 100 / 100
ArClose(Index) = CsvStockTable(i, 4) * 100 / 100
ArVolume(Index) = CsvStockTable(i, 5)
'ArAdjClose(Index) = CsvStockTable(i, 6) * 100 / 100


'**************************************************************************`
' Vérification de l'intégration des tableaux Ar
'Worksheets("AR").Cells(Index + 1, 1) = i
'Worksheets("AR").Cells(Index + 1, 2) = ArDate(Index)
'Worksheets("AR").Cells(Index + 1, 3) = ArOpen(Index)
'Worksheets("AR").Cells(Index + 1, 4) = ArHigh(Index)
'Worksheets("AR").Cells(Index + 1, 5) = ArLow(Index)
'Worksheets("AR").Cells(Index + 1, 6) = ArClose(Index)
'Worksheets("AR").Cells(Index + 1, 7) = ArVolume(Index)
'Worksheets("AR").Cells(Index + 1, 8) = ArAdjClose(Index)
'Worksheets("AR").Cells(Index + 1, 9) = CsvStockTable(i, 0)
'**************************************************************************

Index = Index + 1
i = i + 1
Loop Until (i > Nbr_Jours)


Set Graphe = Charts.Add
'ActiveSheet.Name = STOCK



Set SerieClose = ActiveChart.SeriesCollection.NewSeries
Set SerieVolume = ActiveChart.SeriesCollection.NewSeries


SerieClose.ChartType = xlLine 'xlLine

SerieClose.Name = "Prix"
ActiveChart.SetSourceData Source = ArClose
SerieClose.XValues = ArDate


SerieVolume.Name = "Volume"
SerieVolume.Values = ArVolume
SerieVolume.ChartType = xlColumnClustered
SerieVolume.AxisGroup = 2

End Sub
________________________________________________________________
 
Back
Top