Chart error: plotting series data XY scatter plot

[frostkiller]

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

 

[Martin Brown]

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

 

[Mike Middleton]

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

 

[Jon Peltier]

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