I have a created a vba chart macro that is called from another after a
data table is created for me. Since the number of categories and
series are unknown until after this table is created I had to go this
route.
The purpose is to create a chart that supports both a scatter plot for
the first series (which is always available, unless the table has no
data in which case I do not want to show anything). The other series
(and there can be as many as 1,3,15 or some arbitrary size) should all
be shown as a stacked bar graph.
Below is the code I use for the chart part. Whenever this is run I get
a 400 error. When I step through it, it seems to be coming from the
'.Delete' call on the 4th line. I can't figure out how to fix this
since I don't understand the cause of the error. Also it does not
happen all the time either, but only in certain situations that don't
seem to have any rhyme or reason. Any help would be greatly
appreciated.
Thank you, Alon.
Sub GraphIt()
Set sc = Sheets("Display").ChartObjects("Assets").Chart
With Sheets("Display").ChartObjects("Assets").Chart
.Delete
Set s = .SeriesCollection.NewSeries
End With
s.Name = "=Graphs!R2C2"
s.Values = "=Graphs!R3C2:R" & Sheets("Graphs").Range("C1").Value &
"C2"
s.XValues = "=Graphs!R3C1:R" & Sheets("Graphs").Range("C1").Value &
"C1"
s.ChartType = xlXYScatter
s.MarkerBackgroundColorIndex = 3
s.MarkerForegroundColorIndex = 3
s.MarkerStyle = xlDash
s.MarkerSize = 8
For i = 3 To Sheets("Graphs").Range("D1").Value
With Sheets("Manage").ChartObjects("Assets").Chart
Set s = .SeriesCollection.NewSeries
s.Name = "=Graphs!R2C" & CStr(i)
s.Values = "=Graphs!R3C" & CStr(i) & ":R" &
Sheets("Graphs").Range("C1").Value & "C" & CStr(i)
s.ChartType = xlColumnStacked
s.Border.LineStyle = xlNone
End With
Next i
With Sheets("Display").ChartObjects("Assets").Chart.ChartGroups(1)
.Overlap = 100
.GapWidth = 50
s.XValues = "=Graphs!R3C1:R" & Sheets("Display").Range("C1").Value
& "C1"
End With
End Sub
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
