Setting chart Xvalue errors

[Robert H]

I am building charts with non-contiguous ranges. I have struggled
through allot with this but am stumped at getting
SeriesCollection(#).XValues to work correctly. the different syntax I
have tried either gives me an "unable to set the xvalues property of
the series class" error or the results in the source data Catagory X
axis Labels look like :
={"$H$1","$J$1","$L$1","$N$1","$P$1","$R$1","$T$1","$V$1","$X$1"}
with that curved bracket and sometimes extra quotes...
As you can see by the number of .SeriesCollection(1).XValues = xValRng
variations, I have given up any logical approach and am now easter
egging.

Any help will be met with eternal gratitude!

Sub AddChart()
Dim aChart As Chart
Dim shtNm As String
Dim chtLoc1 As Range
Dim srcRng As Range
Dim hdrRow As Range
Dim numRows As Integer
Dim numColumns As Integer
Dim dataTyp As String
Dim c As Range
Dim firstAdd As String
Dim xVal As String
Dim xValRng As Range

dataTyp = "IMP"
shtNm = ActiveSheet.Name

ActiveSheet.ChartObjects.Delete

Set hdrRow = Range(Range("A1"), Selection.End(xlToRight)) '.Select

With hdrRow
Set c = .find(dataTyp, LookIn:=xlValues)
If Not c Is Nothing Then
firstAdd = c.Address
Do
If c.Address = firstAdd Then
xVal = shtNm & "!" & c.Address
Else
xVal = xVal & "," + shtNm & "!" & c.Address
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAdd
End If
End With

shtNm = ActiveSheet.Name
Set chtLoc1 = Range("dc_res")
Set aChart = Charts.Add
Set aChart = aChart.Location(Where:=xlLocationAsObject,
Name:=shtNm)


With aChart
.ChartType = xlLineMarkers


Set srcRng = Union(Sheets(shtNm).Range("CODE"),
Range("IMP_100_Hz"), Range("IMP_200_Hz"), Range("IMP_400_Hz"), _
Range("IMP_1_kHz"), Range("IMP_2_kHz"), Range("IMP_4_kHz"), _
Range("IMP_10_kHz"), Range("IMP_20_kHz"), Range("IMP_40_kHz"))

.SetSourceData Source:=srcRng, _
PlotBy:=xlRows

Debug.Print xVal

Set xValRng = Range(xVal)
xValRng.Select

'.SeriesCollection(1).XValues = .xValRng.Address
'.SeriesCollection(1).XValues = xValRng.Value
'.SeriesCollection(1).XValues = xValRng
'unable to set the xvalues property of the series class
'.SeriesCollection(1).XValues = xVal
'.SeriesCollection(1).XValues =
Worksheets(shtNm).xValRng.Address
'.SeriesCollection(1).XValues =
Worksheets(shtNm).Range(xVal).Address
'.SeriesCollection(1).XValues = Worksheets(shtNm).xVal
.SeriesCollection(1).XValues = xValRng


.HasTitle = True
.ChartTitle.Text = "Configuration " & shtNm & " Impedance"
With .Parent
.Top = chtLoc1.Offset(10, 0).Top
.Left = chtLoc1.Left
.Height = 252
.Width = 432
.Name = shtNm & "ChartDev"
End With
End With
End Sub

 

[Greg Glynn]

Robert,

I've struck this problem before also. I think there is limit of 10
ranges you can nominate, so check that first. Your code ={"$H$1","$J
$1","$L$1","$N$1","$P$1","$R$1","$T$1","$V$1","$X$1"} only has 9, so
it's probably OK.

The code that worked for me was:

myxvalues = myxvalues & Cells(1, (x * 4)).Value & ","

ActiveChart.SeriesCollection(1).XValues = " " & myxvalues & ")"

That's right! A LEADING SPACE and a TRAILING )

(Don't ask me why it works though).

So try .SeriesCollection(1).XValues = " " & "$H$1","$J$1","$L$1","$N
$1","$P$1","$R$1","$T$1","$V$1","$X$1" & ")"

Let me know how you go.

 

[Greg Glynn]

Here's the Full Code.

I Chart every FOURTH column, so that why there's a lot of mutiplying
and dividing by 4 in the code.

Function ShowMyChart(MyAddress, MyServerName)

MaxGraph = Int(Range(MyAddress).Column / 4)
MyRow = Range(MyAddress).Row
MyColumn = Range(MyAddress).Column
MySeriesName = Cells(MyRow, 2)

If MaxGraph > 10 Then StartData = MaxGraph - 9 Else StartData = 1

For x = StartData To MaxGraph
If Cells(MyRow, (x * 4) + 1).Value <> "" Then
If x < MaxGraph Then
MyDataRange = MyDataRange & Cells(MyRow, (x * 4) + 1).Address & ","
myxvalues = myxvalues & Cells(1, (x * 4)).Value & ","
Else
MyDataRange = MyDataRange & Cells(MyRow, (x * 4) + 1).Address
myxvalues = myxvalues & Cells(1, (x * 4)).Value
End If
End If
Next x

ChartVolume = Cells(MyRow, 2)

Range(MyDataRange).Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection(1).XValues = " " & myxvalues & ")"
ActiveChart.SeriesCollection(1).Name = MySeriesName

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = MyServerName & vbCrLf &
ChartVolume
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "MB"
End With
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Fill.PresetTextured
PresetTexture:=msoTextureBlueTissuePaper
Selection.Fill.Visible = True
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.PlotArea.Select
End Function

 

[Robert H]

thanks for the reply Greg. It will not surprise me if a leading and
training space will fix the problem, Ive seen some strange fixes...
However, it will piss me off hehe. I just went in and recorded a
macro of manually entering the Xvalues and cut that into my code to
get me by so I can finish the analysis I need to do with this data.
(needed to do days ago) I had to make three versions of the code for
three different electrical data types but it works and will save me
loads of time, not having to break out 20 or more sheets and create
several charts on each one! Ill come back when I'm done and work your
suggestion into it so Ill have a more dynamic code I want to open it
up so that I can have several different data types and not have to
write code each time I need to look at a new data type.

Ill post the results

Thanks for the help
Robert

 

[Jon Peltier]

={"$H$1","$J$1","$L$1","$N$1","$P$1","$R$1","$T$1","$V$1","$X$1"}

This means you have put the addresses into an array. You need to produce
what Excel and VBA recognize as a range. You could do it with a range like
this:

ActiveSheet.Range("$H$1,$J$1,$L$1,$N$1,$P$1,$R$1,$T$1,$V$1,$X$1")

Notice the quotes around the entire list of ranges, not around each item.
Your code to apply this range to a series' X values is (where srs is the
object variable denoting the series):

srs.XValues =
Worksheets(shtNm).Range("$H$1,$J$1,$L$1,$N$1,$P$1,$R$1,$T$1,$V$1,$X$1")

or

srs.XValues =
"=(Sheet1!R1C8,Sheet1!R1C10,Sheet1!R1C12,Sheet1!R1C14,Sheet1!R1C14,Sheet1!R1C16,Sheet1!R1C18,Sheet1!R1C20,Sheet1!R1C22)"

Notice the use of R1C1 notation; VBA doesn't let you use A1 notation in this
statement.

The Union you use to define srcRng is inconsistent, because only one of the
ranges is referenced to the worksheet. Try this instead:

With Worksheets(shtNm)
Set srcRng = Union(.Range("CODE"), .Range("IMP_100_Hz"),
..Range("IMP_200_Hz"), .Range("IMP_400_Hz"), _
.Range("IMP_1_kHz"), .Range("IMP_2_kHz"), .Range("IMP_4_kHz"), _
.Range("IMP_10_kHz"), .Range("IMP_20_kHz"), .Range("IMP_40_kHz"))
End With

A more robust solution would be to set up a range in the worksheet which can
be contiguously plotted.

- Jon

 

[Jon Peltier]

The limit is not 10. It depends on the length of the string used to define
the range. His code falls down for other reasons.

Your examples put the values of the indicated cells into a string array,
without preserving links to the original cells. This is fine if the chart is
a one-off and you don't worry about data changing.

The only way inserting a space and a close parenthesis into your string
would work is if the string were somehow missing these characters. The last
line you suggest fails, but this doesn't fail:

activechart.SeriesCollection(1).XValues =
"$H$1,$J$1,$L$1,$N$1,$P$1,$R$1,$T$1,$V$1,$X$1"

It doesn't do as desired, however, as it uses the cell addresses in the
string array, not the values in the cells, as the labels for the category
axis. It converts the entry in the Category Labels box of the Source Data -
Series dialog into exactly what Robert posted.

- Jon