Simple Mismatch Error Question

[Wayne_Golder]

Hi,

I have a spreedsheet with 10 graphs on it.
These graphs have 2 or 3 series on each.
The data is calculate in 1000 rows, but under certain conditions the data becomes invaild. I have setup a single col that checks for where the data become invaild.

I need to stop the graphs plotting beyond the point of validity. What I did for one graph works fine:

For Each mySeries In seriesCol

Set mySeries = ActiveSheet.ChartObjects(J).Chart.SeriesCollection(I)
Select Case mySeries.Name

Case "Luminous Zone"

Def_XValues = "=(Calculations!$C$3:$C$" & Error_From - 1 & ")"
Def_Values = "=(Calculations!$I$3:$I$" & Error_From - 1 & ")"
​

Case "Smoke Layer Height"

Def_XValues = "=(Calculations!$C$3:$C$" & Error_From - 1 & ")"
Def_Values = "=(Calculations!$H$3:$H$" & Error_From - 1 & ")"
​

Case "Critical Height"

Def_XValues = "=(Calculations!$C$3:$C$" & Error_From - 1 & ")"
Def_Values = "=(Calculations!$AJ$3:$AJ$" & Error_From - 1 & ")"
​

Case "Opening Height"

Def_XValues = "=(Calculations!$C$3:$C$" & Error_From - 1 & ")"
Def_Values = "=(Calculations!$AI$3:$AI$" & Error_From - 1 & ")"
​

End Select
​

With mySeries

.XValues = Def_XValues
.Values = Def_Values
​

End With
​

I = I + 1
Next

but when I try to be clever and replace the select case with

For CountDi = 1 To 4

DDDPos = InStr(DDDPos + 1, CStr(mySeries.XValues),"$")
​

Next
Def_XValues = left(CStr(mySeries.XValues),DDDPos) & Error_From - 1 & ")"

it reports I have a type mismatch with the CStr part of:

DDDPos = InStr(DDDPos + 1, CStr(mySeries.XValues),"$")

Cheers

 

[Andrew Poulsom]

The XValues returns an array. Look here for a way to get the range reference:

http://www.j-walk.com/ss/excel/tips/tip83.htm

 

[Wayne_Golder]

ok so it is returning an array of values not the reference, can I get the reference text ? or is that the .address?

I ask as I seem not able to get the .address to work for me but if I call myseries.xvalues then n returns the value at that point.

cheers for the response so far.

 

[Andrew Poulsom]

The SERIESFormulaElement function in the link I posted can return a range reference from a SERIES formula. For example:

MsgBox SERIESFormulaElement(ActiveSheet.ChartObjects(1).Chart, 1, 2)

returns the XValues range reference for the first series of the Chart contained in the first ChartObject on the active sheet.