I have the following data with 23 departments.
The row one department cells are merged.
Link removed by moderation
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.
- Code: Select all
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
Please find my data set at http://peltiertech.com/images/2015-07/M ... rtDeux.png .My data set is similar with 23 pcts and 23 acts.
I have also posted the question on the following links.
http://www.mrexcel.com/forum/excel-ques ... ost4203489
http://www.excelforum.com/excel-general ... ost4120792
http://www.ozgrid.com/forum/showthread. ... post750574
http://stackoverflow.com/questions/3120 ... vba-charts