Hi,
This code will produce your chart.
Don't worry about using the built-in custom type. You can create the
same thing yourself.
Sub Jimmy()
topleft = "E5"
bottomright = "I9"
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Chart").Range( _
topleft & ":" & bottomright), PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"
With ActiveChart
.ChartType = xlLine
.HasTitle = True
.ChartTitle.Characters.Text = "Bid/Quote Success Rate"
' move to secondary axis
For lngseries = 2 To .SeriesCollection.Count
.SeriesCollection(lngseries).AxisGroup = 2
Next
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"# of Bids/Quotes Received"
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = _
"% Submitted of Received or % Won to Date of Submitted/Received"
End With
End Sub
Cheers
Andy
Jimmy wrote:
> Jimmy wrote:
>
>>Thanks for getting back to me, Andy.
>>
>>-------------------------------------------------------------------------------------------------------------
>>XL02
>>-------------------------------------------------------------
>>In this case
>>topright = E5 and
>>bottomleft = Q9
>>
>>The goal is for Row6 to be plotted on the primary axis and for Rows
>>7-9 to be plotted on the secondary access.
>>
>>Here is a smattering of the chart data I use:
>> E F G
>> H I
>>5 Mar-00 Apr-04 Jun-04
>> Apr-05
>>6 # of Bids/Quotes Received 768 117 1,050 172
>>7 % Submitted of Received 17.2% 8.5% 9.6% 19.2%
>>8 % Won to Date of Submitted 66.8% 2.5% 5.9% 8.2%
>>9 % Won to Date of Received 1.5% 2.0% 4.8% 4.3%
>>
>>
>>
>>
>>
>>------------------------------------------------------------------------------------------------------------
>>
>>Another potential problem here is that my enduser has requested a
>>custom-type chart called "Lines on Two Axes" and I am unfamiliar with
>>how to code for this type of chart
>>
>>I am able to create this chart manually. The "record" code in concert
>>with my lack of experience with this type of chart is a problem.
>>
>>Again, thanks-
>>JR
>>
>>---------------------------------------------------------------------------------------------------------------------------
>>
>>
>>Andy Pope wrote:
>>
>>>Hi,
>>>
>>>Does the range defined by topleft and bottomright have enough columns to
>>>have series placed on the secondary axis?
>>>I would imagine trying to set the properties of the secondary axis that
>>>does not exist would cause problems.
>>>
>>>Cheers
>>>Andy
>>>
>>>Jimmy wrote:
>>>
>>>>Hi,
>>>>I am creating a chart in Excel.
>>>>At lines 15-17 (see below) I get
>>>>"Method 'Axes' of Object 'Chart_Chart' failed."
>>>>Any help in getting this chart to create would be greatly appreciated.
>>>>Thanks,
>>>>Jimmy
>>>>
>>>>
>>>>2 Charts.Add
>>>>3 ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
>>>>TypeName:= _
>>>>4 "Lines on 2 Axes"
>>>>5 ActiveChart.SetSourceData
>>>>Source:=Sheets("Chart").Range(topLeft &
>>>>":" & bottomRight), PlotBy:= _
>>>>6 xlRows
>>>>7 ActiveChart.Location Where:=xlLocationAsObject,
>>>>Name:="Chart"
>>>>8 With ActiveChart
>>>>9 .HasTitle = True
>>>>10 .ChartTitle.Characters.Text = "Bid/Quote Success Rate"
>>>>11 .Axes(xlCategory, xlPrimary).HasTitle = False
>>>>12 .Axes(xlValue, xlPrimary).HasTitle = True
>>>>13 .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
>>>>
>>>>14 "# of Bids/Quotes Received"
>>>>15 ' .Axes(xlCategory, xlSecondary).HasTitle = False
>>>>16 ' .Axes(xlValue, xlSecondary).HasTitle = True
>>>>17 ' .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text
>>>>= _
>>>>18 "% Submitted of Received or % Won to Date of
>>>>Submitted/Received"
>>>>19 End With
>>>>
>>>
>>>--
>>>
>>>Andy Pope, Microsoft MVP - Excel
>>>http://www.andypope.info
>
>
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
Similar Threads
