Discontinuous range of X Values

  • Thread starter Thread starter johnhildreth
  • Start date Start date
J

johnhildreth

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
 
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
 
Back
Top