Charting error in Excel2003

[Julie]

I have the following segment of code within a larger method that worked
just fine on Excel 97, but now on Excel 2003, I get a "run-time error
'1004' Method 'PlotBy' of '_Chart' failed" on ActiveChart.PlotBy =
xlColumns. I can comment that line out, but I am not sure if I am
affecting anything else. Does anyone know why this would no longer
work in Excel 2003?

Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Worksheets("NatureOfInjuryChart").Activate
Set r1 = Range("A5",
Worksheets("NatureOfInjuryChart").Range("A5").End(xlDown))
Set r2 = Range("C5",
Worksheets("NatureOfInjuryChart").Range("C5").End(xlDown))
Set myMultiAreaRange = Union(r1, r2)

myMultiAreaRange.Select
Range("C5").Activate

Charts.Add
ActiveChart.ChartType = xl3DPie
ActiveChart.PlotBy = xlColumns
ActiveChart.Location Where:=xlLocationAsObject,
Name:="NatureOfInjuryChart"

#2 Also, this chart (from above) is on the same worksheet as another
chart that is using the range below (columns A & B). However the
calculations for the chart above are exactly the same as the chart
below on Excel 2003, but reflect correct percentages in Excel 97. Does
anyone know why this chart is not producing correct percentages now in
2003?

Range("A5",
Worksheets("NatureOfInjuryChart").Range("B5").End(xlDown)).Select
Range("A5").Activate
Charts.Add
ActiveChart.ChartType = xl3DPie
'ActiveChart.PlotBy = xlColumns
ActiveChart.Location Where:=xlLocationAsObject,
Name:="NatureOfInjuryChart"

This is really stumping me. Any help is greatly appreciated.
Julie

 

[Jon Peltier]

Julie -

1. Did you get a similar chart when you omitted the ActiveChart.PlotBy
line? I would expect that you did. The macro recorder leaves lots of
unnecessary junk in the recorded code.

2. If you increase the decimal digits, do the percentages come out
right? Someone at Microsoft decided it was better to coerce the total to
100%, rather than correctly round the individual values. In a simple
example, three equal wedges are 33.3%, but Excel will show them as 33%,
33%, and 34% if you don't allow extra digits. I had one example last
year in which one percentage label was increased by around 2 percentage
points!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______