Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Discontinuous range of X Values
Anyone know how to set the X Values of a series collection item for a
HLC stock plot to a discontinuous range? I can define a range using the Union function and set the X Values to that range without an error. But the chart is created with nothing defined for X Values. I've also tried to generate a string of addresses. That works, but the displayed values are the addresses, not the values in the cells. Thanks, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Discontinuous range of X Values
Hi John,
Sub Test() Dim n As Long Dim s As String Dim sr As Series Dim rY As Range, rX As Range, cel As Range ' for testing place a chart on the sheet with one dummy series Set sr = ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1) Set rY = Range("B2:B4,B8:B10,B14:B16") For Each cel In rY: cel.Value = cel.Row: Next Set rX = rY.Offset(, -1) n = 64 For Each cel In rX: n = n + 1: cel.Value = Chr(n): Next s = MakeValuesFormula(rY) sr.Values = s s = MakeValuesFormula(rX) sr.XValues = s End Sub Function MakeValuesFormula(rng As Range) As String Dim n As Long Dim s As String Dim ra As Range ' assumes working with the activeworkbook, ' if not need to include [book-name] s = "=" For Each ra In rng.Areas n = n + 1 s = s & "'" & rng.Parent.Name & "'!" s = s & ra.Address(, , xlR1C1) If n < rng.Areas.Count Then s = s & "," Next MakeValuesFormula = s End Function Regards, Peter T wrote in message ups.com... Anyone know how to set the X Values of a series collection item for a HLC stock plot to a discontinuous range? I can define a range using the Union function and set the X Values to that range without an error. But the chart is created with nothing defined for X Values. I've also tried to generate a string of addresses. That works, but the displayed values are the addresses, not the values in the cells. Thanks, John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average range of discontinuous cells | Excel Worksheet Functions | |||
Charting a discontinuous series | Charts and Charting in Excel | |||
sum value of nonblank discontinuous cells | New Users to Excel | |||
Discontinuous Range via VBA using R1C1? | Excel Programming | |||
Discontinuous X axis | Charts and Charting in Excel |