secondary axis - setting -VBA


G

Guest

I hope this is the right user group...

I have a sheet of data for a nuber of projects. I then want to;
create a new sheet for each project and generate a chart on the new sheet.
This is all done with VBA.
It works except that I want to use the secondary axis however the code below
crashes excel when it tries to set the axisgroup.

Can anyone tell me where I am going wrong?

Thanks
Graham


Private Sub GeneratePlots()

Dim i, j
Dim shtPlot As Worksheet
Dim s As Worksheet
Dim rx As Range
Dim ry1 As Range
Dim ry2 As Range
Dim ry3 As Range
Dim ra() As Range
Dim oChart As ChartObject
Dim ChtTop As Long, ChtLeft As Long
Dim ChtHeight As Long, ChtWidth As Long


ChtTop = 20
ChtLeft = 20
ChtWidth = 400
ChtHeight = 400

For i = 1 To 2 ' UBound(mArrUIP, 1)

'add new sheet
mcXL.AddAsLastWorksheet CStr(mArrUIP(i, 1))
Set shtPlot = Worksheets(CStr(mArrUIP(i, 1)))

'add new chart to the new (active) sheet
Set oChart = Worksheets(CStr(mArrUIP(i, 1))).ChartObjects.Add(ChtLeft,
ChtTop, ChtWidth, ChtHeight)
oChart.name = CStr(mArrUIP(i, 1)) & "_1"
oChart.Chart.ChartType = xlLine

'add series
ReDim ra(3)
Set rx = mDataSheet.Range(mDataSheet.Cells(mArrUIP(i, 2), "C"),
mDataSheet.Cells(mArrUIP(i, 3), "C"))
Set ra(1) = mDataSheet.Range(mDataSheet.Cells(mArrUIP(i, 2), "G"),
mDataSheet.Cells(mArrUIP(i, 3), "G"))
Set ra(2) = mDataSheet.Range(mDataSheet.Cells(mArrUIP(i, 2), "K"),
mDataSheet.Cells(mArrUIP(i, 3), "K"))
Set ra(3) = mDataSheet.Range(mDataSheet.Cells(mArrUIP(i, 2), "T"),
mDataSheet.Cells(mArrUIP(i, 3), "T"))

For j = 1 To 3
With oChart.Chart

.SeriesCollection.NewSeries
.SeriesCollection(j).XValues = rx
.SeriesCollection(j).Values = ra(j)

.SeriesCollection(j).MarkerStyle = xlMarkerStyleNone
'.SeriesCollection(j).AxisGroup = xlSecondary <= CRASHES EXCEL

End With
Next

Next i


End Sub
 
Ad

Advertisements

A

Andy Pope

Hi,

It looks like you you are trying to put ALL of the data series on the
secondary axis.
There must be at least 1 series on the primary.

Cheers
Andy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top