Chart error: plotting series data XY scatter plot

F

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
 
M

Martin Brown

frostkiller said:
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
Click to expand...

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
Click to expand...

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
 
J

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
 

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

Similar Threads

Dynamic chart problem 1
How do I plot two curves on a single XY scatter chart? 1
updating data location to build a VBA graph 2
Help setting chart series values with non-continuous range 4
How to change weight of line in XY chart 3
XY scatter plot with more than 32000 rows in Excel 2003 0
Loop to create charts 1
Adding more series to a XY scatter chart 1

Top