Specify location of X Values
 

I am using the following code to build HLC Stock plots for
discontinuous ranges of data. There are 4 ranges plotted and 4 ranges
that are the X value labels. I am successful in referencing each
range and using them to designate the chart source data. But when I
use the Title ranges to set the XValues I get the address string (ie.
$F$6) and not the value in F6. I tried replacing SizeTitle.Address
with SizeTitle.Value (and all remaining Title ranges). This puts the
cell values as XValues, but in the reverse order.

I think the problem is that the XValues needs an array. As the code
is now, sXVal looks like "$F$6,$K$6,$N$6,$R$6" which is an array.

Dim sChartInput As String
Dim s As Series
Dim sXVal As String

sChartInput = SizeRange.Address & "," & MOTRange.Address & _
"," & TOYRange.Address & "," & TIQRange.Address

sXVal = SizeTitle.Address & "," & MOTTitle.Address & _
"," & TOYTitle.Address & "," & TIQTitle.Address

Charts.Add

ActiveChart.SetSourceData
Source:=Sheets("Drainage").Range(sChartInput), PlotBy:=xlRows

For Each s In ActiveChart.SeriesCollection
s.XValues = sXVal
Next s


Thanks for any help,
John

Specify location of X Values
 

I might try a range variable:

Dim rXVal as Range
rXVal = union(SizeTitle, MOTTitle, TOYTitle, TIQTitle)
s.XValues = rXVal

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
oups.com...
I am using the following code to build HLC Stock plots for
discontinuous ranges of data. There are 4 ranges plotted and 4 ranges
that are the X value labels. I am successful in referencing each
range and using them to designate the chart source data. But when I
use the Title ranges to set the XValues I get the address string (ie.
$F$6) and not the value in F6. I tried replacing SizeTitle.Address
with SizeTitle.Value (and all remaining Title ranges). This puts the
cell values as XValues, but in the reverse order.

I think the problem is that the XValues needs an array. As the code
is now, sXVal looks like "$F$6,$K$6,$N$6,$R$6" which is an array.

Dim sChartInput As String
Dim s As Series
Dim sXVal As String

sChartInput = SizeRange.Address & "," & MOTRange.Address & _
"," & TOYRange.Address & "," & TIQRange.Address

sXVal = SizeTitle.Address & "," & MOTTitle.Address & _
"," & TOYTitle.Address & "," & TIQTitle.Address

Charts.Add

ActiveChart.SetSourceData
Source:=Sheets("Drainage").Range(sChartInput), PlotBy:=xlRows

For Each s In ActiveChart.SeriesCollection
s.XValues = sXVal
Next s


Thanks for any help,
John