Hi all,

I am having trouble with my chart making macros. I am trying to create a
series XY-scatter plot of several columns (in this particular case 13
columns). My code if giving me an error, see code below between the double
asterisk highlight **...**

Do I need to plot each series at a time? Any help would be greatly
appreciated. Thanks.


For Each c In Worksheets("shift").Range(Cells(2, 2), Cells(lastRow,
lastCol)).Cells
If c.Value = "" Then c.Value = 0
Next

' here I am setting each empty cell to zero

Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="shift"
ActiveChart.ChartType = xlXYScatter
ChartOne = ActiveChart.Parent.Name
**Worksheets("shift").ActiveChart.SetSourceData Source:=Worksheets
("shift").Range(Cells(2, 2), Cells(lastRow, lastCol)), _
PlotBy:=xlColumns**

' here is where the code errors

ActiveChart.PlotArea.Select
Selection.Interior.ColorIndex = xlNone
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With

frostkiller wrote:
> Hi all,
>
> I am having trouble with my chart making macros. I am trying to create a
> series XY-scatter plot of several columns (in this particular case 13
> columns). My code if giving me an error, see code below between the double
> asterisk highlight **...**
>
> Do I need to plot each series at a time? Any help would be greatly
> appreciated. Thanks.
>
>
> For Each c In Worksheets("shift").Range(Cells(2, 2), Cells(lastRow,
> lastCol)).Cells
> If c.Value = "" Then c.Value = 0
> Next
>
> ' here I am setting each empty cell to zero

Not sure why you bother. An empty cell is actually marginally safer
since you don't get the stupid error message with log Y scales.
>
> Charts.Add
> ActiveChart.Location Where:=xlLocationAsObject, Name:="shift"
> ActiveChart.ChartType = xlXYScatter
> ChartOne = ActiveChart.Parent.Name
> **Worksheets("shift").ActiveChart.SetSourceData Source:=Worksheets
> ("shift").Range(Cells(2, 2), Cells(lastRow, lastCol)), _
> PlotBy:=xlColumns**
>
> ' here is where the code errors
>
> ActiveChart.PlotArea.Select
> Selection.Interior.ColorIndex = xlNone
> With Selection.Border
> .ColorIndex = 16
> .Weight = xlThin
> .LineStyle = xlContinuous
> End With

This looks like the result of record macro capture. Unfortunately not
everything works as advertised. And in XL2007 almost nothing does

Questions:
What are the values of lastRow, lastCol?
Running what version of Excel ?
Does it still fail if you step through it line by line in the debugger?

XL2007 is littered with race conditions where charts will not allow
selection of sub-objects until they have been fully instantiated and
initialised. This creates additional delays. Workarounds are documented.

I found this sequence to be least prone to race conditions YMMV

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets(sheetname).Range(s), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.PlotArea.Interior.Color = RGB(255, 255, 255)

Regards,
Martin Brown

frostkiller -

An excellent source of information about using VBA for Excel charts is Jon
Peltier's pages:

http://peltiertech.com/Excel/Charts/chartvba.html

- Mike
http://www.MikeMiddleton.com



"frostkiller" <(E-Mail Removed)> wrote in message
news:111D00C8-7EA2-43A7-BC82-(E-Mail Removed)...
> Hi all,
>
> I am having trouble with my chart making macros. I am trying to create a
> series XY-scatter plot of several columns (in this particular case 13
> columns). My code if giving me an error, see code below between the double
> asterisk highlight **...**
>
> Do I need to plot each series at a time? Any help would be greatly
> appreciated. Thanks.
>
>
> For Each c In Worksheets("shift").Range(Cells(2, 2), Cells(lastRow,
> lastCol)).Cells
> If c.Value = "" Then c.Value = 0
> Next
>
> ' here I am setting each empty cell to zero
>
> Charts.Add
> ActiveChart.Location Where:=xlLocationAsObject, Name:="shift"
> ActiveChart.ChartType = xlXYScatter
> ChartOne = ActiveChart.Parent.Name
> **Worksheets("shift").ActiveChart.SetSourceData Source:=Worksheets
> ("shift").Range(Cells(2, 2), Cells(lastRow, lastCol)), _
> PlotBy:=xlColumns**
>
> ' here is where the code errors
>
> ActiveChart.PlotArea.Select
> Selection.Interior.ColorIndex = xlNone
> With Selection.Border
> .ColorIndex = 16
> .Weight = xlThin
> .LineStyle = xlContinuous
> End With

Here's one problem (there may be others):

Worksheets("shift").ActiveChart.SetSourceData

A worksheet doesn't have an active chart. Just use ActiveChart.SetSourceData

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/14/2010 2:26 PM, frostkiller wrote:
> Hi all,
>
> I am having trouble with my chart making macros. I am trying to create a
> series XY-scatter plot of several columns (in this particular case 13
> columns). My code if giving me an error, see code below between the double
> asterisk highlight **...**
>
> Do I need to plot each series at a time? Any help would be greatly
> appreciated. Thanks.
>
>
> For Each c In Worksheets("shift").Range(Cells(2, 2), Cells(lastRow,
> lastCol)).Cells
> If c.Value = "" Then c.Value = 0
> Next
>
> ' here I am setting each empty cell to zero
>
> Charts.Add
> ActiveChart.Location Where:=xlLocationAsObject, Name:="shift"
> ActiveChart.ChartType = xlXYScatter
> ChartOne = ActiveChart.Parent.Name
> **Worksheets("shift").ActiveChart.SetSourceData Source:=Worksheets
> ("shift").Range(Cells(2, 2), Cells(lastRow, lastCol)), _
> PlotBy:=xlColumns**
>
> ' here is where the code errors
>
> ActiveChart.PlotArea.Select
> Selection.Interior.ColorIndex = xlNone
> With Selection.Border
> .ColorIndex = 16
> .Weight = xlThin
> .LineStyle = xlContinuous
> End With