Series formula too long error in VBA charts

[adventurer]

Hi,

I have the following data with 23 departments.
The row one department cells are merged.

I am trying to make two charts using VBA; one for Actual and one For % of total by the following code. I am getting two graphs until in the For loop of i Column the values do not exceed 35 and 36 respectively in place of 29 and 30 respectively. As I exceed the values 35 and 36 resp, it shows 'Series Formula Too Long' error and the grphs are not plotted. Please tell me why this is happening and what is the solution for the same.
Thanks.




Sub TwoCharts11()


Dim rChart1 As Range
Dim rChart2 As Range
Dim iColumn As Long


Dim cht1 As Chart
Dim cht2 As Chart


Const StrtRow As Long = 2
Const EndRow As Long = 6


With ActiveSheet


Set rChart1 = .Range(.Cells(StrtRow, "B"), .Cells(EndRow, "B"))
Set rChart2 = .Range(.Cells(StrtRow, "B"), .Cells(EndRow, "B"))


For iColumn = 3 To 29 Step 2
Set rChart1 = Union(rChart1, .Range(.Cells(StrtRow, iColumn), .Cells(EndRow, iColumn)))
Next


For iColumn = 4 To 30 Step 2
Set rChart2 = Union(rChart2, .Range(.Cells(StrtRow, iColumn), .Cells(EndRow, iColumn)))
Next


Set cht1 = .Shapes.AddChart.Chart
Set cht2 = .Shapes.AddChart.Chart

With cht1
.Parent.Left = .Parent.Left - .Parent.Width / 2
.ChartType = xlColumnClustered
.SetSourceData rChart1
End With

With cht2
.Parent.Left = .Parent.Left + .Parent.Width / 2
.ChartType = xlColumnClustered
.SetSourceData rChart2
End With

End With




End Sub

 

[adventurer]

Hi,
Do find the image here for the above question.

 

[adventurer]

Hi,

The images are not visible to others. So kindly give your email addresses so that I can mail the image to you. Or you can also find the similar data set at

for example my data set has 23 pcts and 23 acts as above.

 

[arthurbr]

Cross post
Series formula too long error in VBA charts
Series formula too long error in VBA charts • ExcelKey.com - Free Excel Help

 

[adventurer]

Hi,

The images are not visible to others. So kindly give your email addresses so that I can mail the image to you. Or you can also find the similar data set at

for example my data set has 23 pcts and 23 acts as above.

I have also posted the question on http://www.excelkey.com/forum/viewtopic.php?f=3&t=7003&p=22008#p22008

http://www.ozgrid.com/forum/showthread.php?t=195589&p=750574#post750574

http://www.excelforum.com/excel-gen...too-long-error-in-vba-charts.html#post4120792

 

[adventurer]

Question also posted at excel vba - Series formula too long error in VBA charts - Stack Overflow