Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Discontiguous Chart Source
I would like to insert a pie chart where the source data is on the active
worksheet but comes from two discontiguous ranges (eg B2:C10 and B15:C15). I have two ranges GBData, for the first range, and NonGBData for the second range. The following code sets the source data as B2:C15, which is incorrect. How can I amend it to create the chart with only the data I want? WShtName = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlPie ActiveChart.SetSourceData Source:=Sheets(WShtName).Range( _ GBData.Address, NonGBData.Address), PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:=WShtName Many thanks for any suggestions, happy New Year! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Discontiguous Chart Source
Ladymuck,
You could create a string that concatenates those range addresses & separates them via a comma. -- http://HelpExcel.com "Ladymuck" wrote: I would like to insert a pie chart where the source data is on the active worksheet but comes from two discontiguous ranges (eg B2:C10 and B15:C15). I have two ranges GBData, for the first range, and NonGBData for the second range. The following code sets the source data as B2:C15, which is incorrect. How can I amend it to create the chart with only the data I want? WShtName = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlPie ActiveChart.SetSourceData Source:=Sheets(WShtName).Range( _ GBData.Address, NonGBData.Address), PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:=WShtName Many thanks for any suggestions, happy New Year! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Discontiguous Chart Source
Even manually, those ranges don't come out properly, but to get the string
you want to get: ActiveChart.SetSourceData Source:=Sheets(WShtName).Range( _ GBData.Address & "," & NonGBData.Address) -- Regards, Tom Ogilvy "Ladymuck" wrote in message ... I would like to insert a pie chart where the source data is on the active worksheet but comes from two discontiguous ranges (eg B2:C10 and B15:C15). I have two ranges GBData, for the first range, and NonGBData for the second range. The following code sets the source data as B2:C15, which is incorrect. How can I amend it to create the chart with only the data I want? WShtName = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlPie ActiveChart.SetSourceData Source:=Sheets(WShtName).Range( _ GBData.Address, NonGBData.Address), PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:=WShtName Many thanks for any suggestions, happy New Year! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Discontiguous Chart Source
Try this in a new workbook -
Sub test2() With Range("B2:C10") .Name = "GBData" .Columns(1).Value = "a" .Columns(2).Value = 1 End With With Range("B15:C15") .Name = "NonGBData" .Columns(1).Value = "B" .Columns(2).Value = 4 End With ' above for testing, real stuff below Dim sX As String Dim rYvals As Range Dim rTL As Range Set rYvals = Union(Range("GBData").Columns(2), Range("NonGBData").Columns(2)) sX = "=(" & Range("GBData").Columns(1).Address(, , xlR1C1, True) & "," sX = sX & Range("NonGBData").Columns(1).Address(, , xlR1C1, True) & ")" Set rTL = Range("e2") With ActiveSheet.ChartObjects.Add(rTL.Left, rTL.Top, 200, 200) With .Chart .ChartType = xlPie .SetSourceData _ Source:=rYvals, _ PlotBy:=xlColumns .SeriesCollection(1).XValues = sX ' more chart properties End With End With End Sub I first tried to set the source simpy as ..Source:= Union(Range("GBData")., Range("NonGBData") However this sets the Y-values as columns 1 of the respective ranges with no category X labels, btw I assume XValues are in the first column-B of the respective ranges with the y-values in column C If you want your chart to be 'dynamic', ie to update with dynamic named ranges, would need separate named ranges for the X & Y values, and instead of the addresses syntax along the lines of mybook.xls!myname Regards, Peter T "Ladymuck" wrote in message ... I would like to insert a pie chart where the source data is on the active worksheet but comes from two discontiguous ranges (eg B2:C10 and B15:C15). I have two ranges GBData, for the first range, and NonGBData for the second range. The following code sets the source data as B2:C15, which is incorrect. How can I amend it to create the chart with only the data I want? WShtName = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlPie ActiveChart.SetSourceData Source:=Sheets(WShtName).Range( _ GBData.Address, NonGBData.Address), PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:=WShtName Many thanks for any suggestions, happy New Year! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Discontiguous Chart Source
I first tried to set the source simpy as
.Source:= Union(Range("GBData")., Range("NonGBData") I wasn't thinking, that does work if the top left cell is empty Sub test3() With Range("B2:C10") .Name = "GBData" .Columns(1).Value = "a" .Columns(2).Value = 1 .Cells(1, 1) = "" .Cells(1, 2) = "my Pie" End With With Range("B15:C15") .Name = "NonGBData" .Columns(1).Value = "B" .Columns(2).Value = 4 End With Dim rTL As Range Set rTL = Range("e2") With ActiveSheet.ChartObjects.Add(rTL.Left, rTL.Top, 200, 200) With .Chart .ChartType = xlPie .SetSourceData _ Source:=Union(Range("GBData"), Range("NonGBData")), _ PlotBy:=xlColumns End With End With End Sub Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Try this in a new workbook - Sub test2() With Range("B2:C10") .Name = "GBData" .Columns(1).Value = "a" .Columns(2).Value = 1 End With With Range("B15:C15") .Name = "NonGBData" .Columns(1).Value = "B" .Columns(2).Value = 4 End With ' above for testing, real stuff below Dim sX As String Dim rYvals As Range Dim rTL As Range Set rYvals = Union(Range("GBData").Columns(2), Range("NonGBData").Columns(2)) sX = "=(" & Range("GBData").Columns(1).Address(, , xlR1C1, True) & "," sX = sX & Range("NonGBData").Columns(1).Address(, , xlR1C1, True) & ")" Set rTL = Range("e2") With ActiveSheet.ChartObjects.Add(rTL.Left, rTL.Top, 200, 200) With .Chart .ChartType = xlPie .SetSourceData _ Source:=rYvals, _ PlotBy:=xlColumns .SeriesCollection(1).XValues = sX ' more chart properties End With End With End Sub I first tried to set the source simpy as .Source:= Union(Range("GBData")., Range("NonGBData") However this sets the Y-values as columns 1 of the respective ranges with no category X labels, btw I assume XValues are in the first column-B of the respective ranges with the y-values in column C If you want your chart to be 'dynamic', ie to update with dynamic named ranges, would need separate named ranges for the X & Y values, and instead of the addresses syntax along the lines of mybook.xls!myname Regards, Peter T "Ladymuck" wrote in message ... I would like to insert a pie chart where the source data is on the active worksheet but comes from two discontiguous ranges (eg B2:C10 and B15:C15). I have two ranges GBData, for the first range, and NonGBData for the second range. The following code sets the source data as B2:C15, which is incorrect. How can I amend it to create the chart with only the data I want? WShtName = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlPie ActiveChart.SetSourceData Source:=Sheets(WShtName).Range( _ GBData.Address, NonGBData.Address), PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:=WShtName Many thanks for any suggestions, happy New Year! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Discontiguous Chart Source
Thanks for all your suggestions, I'll give them a try and let you know how I
get on! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Discontiguous Chart Source
Thanks Tim, very simple solution and it worked a treat. Can't believe I
missed that. Cheers! "Tom Ogilvy" wrote: Even manually, those ranges don't come out properly, but to get the string you want to get: ActiveChart.SetSourceData Source:=Sheets(WShtName).Range( _ GBData.Address & "," & NonGBData.Address) -- Regards, Tom Ogilvy "Ladymuck" wrote in message ... I would like to insert a pie chart where the source data is on the active worksheet but comes from two discontiguous ranges (eg B2:C10 and B15:C15). I have two ranges GBData, for the first range, and NonGBData for the second range. The following code sets the source data as B2:C15, which is incorrect. How can I amend it to create the chart with only the data I want? WShtName = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlPie ActiveChart.SetSourceData Source:=Sheets(WShtName).Range( _ GBData.Address, NonGBData.Address), PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:=WShtName Many thanks for any suggestions, happy New Year! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Items in a discontiguous range | Excel Programming | |||
Is it possible to plot discontiguous cells? | Charts and Charting in Excel | |||
Is it possible to plot discontiguous cells? | Excel Discussion (Misc queries) | |||
discontiguous ranges in charts | Excel Programming | |||
Defining a discontiguous Range object | Excel Programming |