P
P. Lui
Hi,
I want to create a graph that has 3 series and each series has 60
points. When I tried to do this through the chart wizard, it gives me
an error saying the formula is too long. Upon doing some research, I
found that the references of the points can't be longer than 255
characters. So I decided to do it through a VB script. Below is the
script:
Sub graph()
Sheets("new avg_en chart").Select
Charts.Add
ActiveChart.ChartType = xl3DColumn
ActiveChart.SetSourceData Source:=Sheets("new avg_en
chart").Range("C2,C5,C8,C11,C14,C17,C20,C23,C26,C29,C32,C35,C38,C41,C44,C47,C50,C53,C56,C59,C62,C65,C68,C71,C74,C77,C80,C83,C86,C89,C92,C95,C98,C101,C104,C107,C110,C113,C116,C119,C122,C125,C128,C131,C134,C137,C140,C143,C146,C149,C152,C155,C158,C161,C164,C167,C170,C173,C176,C179"),
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="new avg_en
chart"
With ActiveChart
.HasTitle = False
.Axes(xlCategory).HasTitle = False
.Axes(xlSeries).HasTitle = False
.Axes(xlValue).HasTitle = False
End With
End Sub
When I tried to run it, it gives me a Run Time Error 1004 on the line
where I listed all the points.
P.S. My ultimate goal is this: I have monthly data of 5 variables and
three datasets for each of the 5 variables. I would like to have a
column graph that goes right on top of each other to show the
difference among them (not stacked but overlapped). There doesn't
seem to be an option for me to do this in Excel so I decided to use 3D
columns and change the persective to facing directly at the columns to
give that overlap look. However, the fills inside the column gives
another problem. Is there a better of a doing this? In the worst
scenario, I could just leave the fill as tranparent and color them by
hand. But if there's a better of doing this, I would like to know.
Thanks!
I want to create a graph that has 3 series and each series has 60
points. When I tried to do this through the chart wizard, it gives me
an error saying the formula is too long. Upon doing some research, I
found that the references of the points can't be longer than 255
characters. So I decided to do it through a VB script. Below is the
script:
Sub graph()
Sheets("new avg_en chart").Select
Charts.Add
ActiveChart.ChartType = xl3DColumn
ActiveChart.SetSourceData Source:=Sheets("new avg_en
chart").Range("C2,C5,C8,C11,C14,C17,C20,C23,C26,C29,C32,C35,C38,C41,C44,C47,C50,C53,C56,C59,C62,C65,C68,C71,C74,C77,C80,C83,C86,C89,C92,C95,C98,C101,C104,C107,C110,C113,C116,C119,C122,C125,C128,C131,C134,C137,C140,C143,C146,C149,C152,C155,C158,C161,C164,C167,C170,C173,C176,C179"),
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="new avg_en
chart"
With ActiveChart
.HasTitle = False
.Axes(xlCategory).HasTitle = False
.Axes(xlSeries).HasTitle = False
.Axes(xlValue).HasTitle = False
End With
End Sub
When I tried to run it, it gives me a Run Time Error 1004 on the line
where I listed all the points.
P.S. My ultimate goal is this: I have monthly data of 5 variables and
three datasets for each of the 5 variables. I would like to have a
column graph that goes right on top of each other to show the
difference among them (not stacked but overlapped). There doesn't
seem to be an option for me to do this in Excel so I decided to use 3D
columns and change the persective to facing directly at the columns to
give that overlap look. However, the fills inside the column gives
another problem. Is there a better of a doing this? In the worst
scenario, I could just leave the fill as tranparent and color them by
hand. But if there's a better of doing this, I would like to know.
Thanks!