Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help setting chart series values with non-continuous range
I am trying to create a chart with multiple series and each series having
values from non-continuous ranges. Can someone help by explaining why this doesn't work or what I should do instead. Charts.Add ActiveChart.ChartType = xlLineMarkers With ActiveChart.SeriesCollection.NewSeries .Name = Worksheets("Review").Cells(vX, 2) .Values = Union(Range(Worksheets("Review").Cells(11, 7), Worksheets("Review").Cells(11, 24)), Range(Worksheets("Review").Cells(50, 25), Worksheets("Review").Cells(50, 61))) End With I found the Uniton on this forum and it works for ActiveChart.SetSourceData Source:= But I need to do multiple series. Thanks, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help setting chart series values with non-continuous range
Have you thought about defining a non contiguous named range and then
applying that to the series? "John Hutcins" wrote: I am trying to create a chart with multiple series and each series having values from non-continuous ranges. Can someone help by explaining why this doesn't work or what I should do instead. Charts.Add ActiveChart.ChartType = xlLineMarkers With ActiveChart.SeriesCollection.NewSeries .Name = Worksheets("Review").Cells(vX, 2) .Values = Union(Range(Worksheets("Review").Cells(11, 7), Worksheets("Review").Cells(11, 24)), Range(Worksheets("Review").Cells(50, 25), Worksheets("Review").Cells(50, 61))) End With I found the Uniton on this forum and it works for ActiveChart.SetSourceData Source:= But I need to do multiple series. Thanks, John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help setting chart series values with non-continuous range
I can't say this is the best way, but it worked for me:
Sub AAA() Dim vx as Long, sh as Worksheet vx = 1 Charts.Add ActiveChart.ChartType = xlLineMarkers Set sh = Worksheets("Review") With ActiveChart.SeriesCollection.NewSeries .Name = sh.Cells(vx, 2) .Formula = "=SERIES(,,(" & sh.Range( _ sh.Cells(11, 7), _ sh.Cells(11, 24)) _ .Address(1, 1, xlA1, True) & "," & _ sh.Range(sh.Cells(50, 25), _ sh.Cells(50, 61)) _ .Address(1, 1, xlA1, True) & "),1)" End With End Sub -- Regards, Tom Ogilvy "John Hutcins" wrote: I am trying to create a chart with multiple series and each series having values from non-continuous ranges. Can someone help by explaining why this doesn't work or what I should do instead. Charts.Add ActiveChart.ChartType = xlLineMarkers With ActiveChart.SeriesCollection.NewSeries .Name = Worksheets("Review").Cells(vX, 2) .Values = Union(Range(Worksheets("Review").Cells(11, 7), Worksheets("Review").Cells(11, 24)), Range(Worksheets("Review").Cells(50, 25), Worksheets("Review").Cells(50, 61))) End With I found the Uniton on this forum and it works for ActiveChart.SetSourceData Source:= But I need to do multiple series. Thanks, John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help setting chart series values with non-continuous range
In general:
With Worksheets("Review") Set rngCht = Union(.Range(.Cells(11, 7), .Cells(11, 24)), ..Range(.Cells(50, 25), .Cells(50, 61))) End With With ActiveChart.SeriesCollection.NewSeries .Name = Worksheets("Review").Cells(vX, 2) .Values = rngCht End With but Excel doesn't let you use a range that isn't a single row or a single column for values, and your values are in rows 11 and 50. I generally prefer to work with .Values and .XValues than with the series .Formula, but here it's unavoidable. Use Tom's procedure. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John Hutcins" wrote in message ... I am trying to create a chart with multiple series and each series having values from non-continuous ranges. Can someone help by explaining why this doesn't work or what I should do instead. Charts.Add ActiveChart.ChartType = xlLineMarkers With ActiveChart.SeriesCollection.NewSeries .Name = Worksheets("Review").Cells(vX, 2) .Values = Union(Range(Worksheets("Review").Cells(11, 7), Worksheets("Review").Cells(11, 24)), Range(Worksheets("Review").Cells(50, 25), Worksheets("Review").Cells(50, 61))) End With I found the Uniton on this forum and it works for ActiveChart.SetSourceData Source:= But I need to do multiple series. Thanks, John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help setting chart series values with non-continuous range
Thanks, this did it for me! Although, I had to move the .Name = declaration
to after .Formula = . For some reason it would take the name assigment after but not before.. Either way that got it done, thanks. I may also try the Set rngCht = suggested by Jon to set the value instead of the formula. Always greate help here! "Tom Ogilvy" wrote: I can't say this is the best way, but it worked for me: Sub AAA() Dim vx as Long, sh as Worksheet vx = 1 Charts.Add ActiveChart.ChartType = xlLineMarkers Set sh = Worksheets("Review") With ActiveChart.SeriesCollection.NewSeries .Name = sh.Cells(vx, 2) .Formula = "=SERIES(,,(" & sh.Range( _ sh.Cells(11, 7), _ sh.Cells(11, 24)) _ .Address(1, 1, xlA1, True) & "," & _ sh.Range(sh.Cells(50, 25), _ sh.Cells(50, 61)) _ .Address(1, 1, xlA1, True) & "),1)" End With End Sub -- Regards, Tom Ogilvy "John Hutcins" wrote: I am trying to create a chart with multiple series and each series having values from non-continuous ranges. Can someone help by explaining why this doesn't work or what I should do instead. Charts.Add ActiveChart.ChartType = xlLineMarkers With ActiveChart.SeriesCollection.NewSeries .Name = Worksheets("Review").Cells(vX, 2) .Values = Union(Range(Worksheets("Review").Cells(11, 7), Worksheets("Review").Cells(11, 24)), Range(Worksheets("Review").Cells(50, 25), Worksheets("Review").Cells(50, 61))) End With I found the Uniton on this forum and it works for ActiveChart.SetSourceData Source:= But I need to do multiple series. Thanks, John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named range not continuous. How to display values? | Excel Worksheet Functions | |||
VBA - Setting up a Chart series | Excel Discussion (Misc queries) | |||
Read series values range of a chart | Charts and Charting in Excel | |||
Setting Excel chart series from vb6 | Charts and Charting in Excel | |||
Setting Chart series | Excel Programming |