Home Menu

Menu


ExcelBanter » Excel Newsgroups » Charts and Charting in Excel > Unable to Set the XValues Property of the Series
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 20
Default Unable to Set the XValues Property of the Series
Hello everyone..... I'm stymied by the error message and have not been
able to solve it for days. If you can, please help!

Here's the situation: I have 2 embedded charts on one worksheet.
One's linear ("ClickChartLinear") and one's log ("ClickChartLog").
Each chart has 23 series already created. The last 3--Trend1, Trend2,
and Trend3--were originally just Pt to Pt lines. The source cells
always stayed the same but the values in them could be changed by the
user. The user clicks a Trend button and clicks 2 pts on the Price
series line. The code gets the From/To dates and prices of a stock
from the clicked points and places those in the source cells. This
worked fine on both Linear & Log.

Then someone suggested I extend the line out into the future. OK, so I
let the user choose "Pt to Pt" or "Expand". Expand data is calculated
via worksheet formulas. The user goes through the clicking process,
then based on what was selected, one of 2 Subs is run to assign the
proper source data.

The Expand reassignments work fine for Log & Linear. Pt to Pt works
for Linear, but not for Log.
The code is below. Complicating matter is the fact that I created
these Trend lines a good while ago and don't remember how/why I've
ended up with different boxes in Excel's source data screen.

In the Linear window, I've got XValues, YValues, and Category (X)
Labels. In the Log window, I've only got XValues and YValues. Does
this matter?

Ex: Say my chart X axis runs from Jan 75 through Jan 11. The user
could select to draw a line from Feb 86 to Aug 90. So the Expand line
would actually go from Feb 86 to Jan 11 while the Pt to Pt line would
stop at Aug 90.

Here are the 2 Subs. The first one...no problem. Oh, and I used to
have Sub2 like
If trend 1
draw Linear
draw Log
elseif...

but decided to split it out to see if it made a difference.

If you need more info, let me know. THANK YOU SO MUCH........Kathy



Sub AssignTrendLines() ' This plots the line using 2 entered end points
and the future points
' extrapolated from using the TREND function on
the worksheet
'
If CEctr = 7 Then ' Trend1
TLR = Range("AH39").Value
Range("AO3").Value = "=INDIRECT($AG$39)*(1+$F$17)^$AR3"
Range("AO3:AO" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20) ' col t dates/ col AO
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C41:R" + TLR + "C41"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20)
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C41:R" + TLR + "C41"
End With
ElseIf CEctr = 9 Then ' Trend2
TLR = Range("AH40").Value
Range("AP3").Value = "=INDIRECT($AG$40)*(1+$F$18)^$AS3"
Range("AP3:AP" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AP
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C42:R" + TLR + "C42"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AP
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C42:R" + TLR + "C42"
End With
ElseIf CEctr = 11 Then ' Trend3
TLR = Range("AH41").Value
Range("AQ3").Value = "=INDIRECT($AG$41)*(1+$F$19)^$AT3"
Range("AQ3:AQ" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C43:R" + TLR + "C43"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C43:R" + TLR + "C43"
End With
End If
End Sub

Sub AssignPtLines()
If CEctr = 7 Then ' Trend1
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R17C2:R17C3"
.Values = "=ClickChart!R17C4:R17C5"
End With
ElseIf CEctr = 9 Then ' Trend2
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R18C2:R18C3"
.Values = "=ClickChart!R18C4:R18C5"
End With
ElseIf CEctr = 11 Then ' Trend3
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R19C2:R19C3"
.Values = "=ClickChart!R19C4:R19C5"
End With
End If

If CEctr = 7 Then ' Trend1
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20)
.XValues = "=ClickChart!R17C2:R17C3" ' TRASHING HERE!!!
.Values = "=ClickChart!R17C4:R17C5"
End With
ElseIf CEctr = 9 Then ' Trend2
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) 'same for log
.XValues = "=ClickChart!R18C2:R18C3"
.Values = "=ClickChart!R18C4:R18C5"
End With
ElseIf CEctr = 11 Then ' Trend3
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' same for log
.XValues = "=ClickChart!R19C2:R19C3"
.Values = "=ClickChart!R19C4:R19C5"
End With
End If
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 6,582
Default Unable to Set the XValues Property of the Series
You didn't indicate which line failed. This error message is commonly
encountered if for some reason the Line or XY chart series is not plotted,
for example if the entire range contained blanks or errors. Since the series
doesn't really exist, VBA chokes when trying to assign XValues or Values
(XValues is listed first, which is why it failed; Values would have failed
if it was first). Workarounds include making sure there's always data in
these ranges, or temporarily changing the troublesome series to another type
(area or column) before assigning XValues and Values, then changing back to
XY (or line, but you should be using XY if you're doing trends).

In the Linear window, I've got XValues, YValues, and Category (X)
Labels. In the Log window, I've only got XValues and YValues. Does
this matter?

This might mean the first type is a Line chart and the second is an XY
chart. For decent fitted lines, you should always use XY charts.

.XValues = "=ClickChart!R3C20:R" + TLR + "C20"

You should use & not + for string manipulation. The plus sign usually works,
until it doesn't.

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


"KathyC" wrote in message
oups.com...
Hello everyone..... I'm stymied by the error message and have not been
able to solve it for days. If you can, please help!

Here's the situation: I have 2 embedded charts on one worksheet.
One's linear ("ClickChartLinear") and one's log ("ClickChartLog").
Each chart has 23 series already created. The last 3--Trend1, Trend2,
and Trend3--were originally just Pt to Pt lines. The source cells
always stayed the same but the values in them could be changed by the
user. The user clicks a Trend button and clicks 2 pts on the Price
series line. The code gets the From/To dates and prices of a stock
from the clicked points and places those in the source cells. This
worked fine on both Linear & Log.

Then someone suggested I extend the line out into the future. OK, so I
let the user choose "Pt to Pt" or "Expand". Expand data is calculated
via worksheet formulas. The user goes through the clicking process,
then based on what was selected, one of 2 Subs is run to assign the
proper source data.

The Expand reassignments work fine for Log & Linear. Pt to Pt works
for Linear, but not for Log.
The code is below. Complicating matter is the fact that I created
these Trend lines a good while ago and don't remember how/why I've
ended up with different boxes in Excel's source data screen.

In the Linear window, I've got XValues, YValues, and Category (X)
Labels. In the Log window, I've only got XValues and YValues. Does
this matter?

Ex: Say my chart X axis runs from Jan 75 through Jan 11. The user
could select to draw a line from Feb 86 to Aug 90. So the Expand line
would actually go from Feb 86 to Jan 11 while the Pt to Pt line would
stop at Aug 90.

Here are the 2 Subs. The first one...no problem. Oh, and I used to
have Sub2 like
If trend 1
draw Linear
draw Log
elseif...

but decided to split it out to see if it made a difference.

If you need more info, let me know. THANK YOU SO MUCH........Kathy



Sub AssignTrendLines() ' This plots the line using 2 entered end points
and the future points
' extrapolated from using the TREND function on
the worksheet
'
If CEctr = 7 Then ' Trend1
TLR = Range("AH39").Value
Range("AO3").Value = "=INDIRECT($AG$39)*(1+$F$17)^$AR3"
Range("AO3:AO" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20) ' col t dates/ col AO
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C41:R" + TLR + "C41"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20)
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C41:R" + TLR + "C41"
End With
ElseIf CEctr = 9 Then ' Trend2
TLR = Range("AH40").Value
Range("AP3").Value = "=INDIRECT($AG$40)*(1+$F$18)^$AS3"
Range("AP3:AP" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AP
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C42:R" + TLR + "C42"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AP
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C42:R" + TLR + "C42"
End With
ElseIf CEctr = 11 Then ' Trend3
TLR = Range("AH41").Value
Range("AQ3").Value = "=INDIRECT($AG$41)*(1+$F$19)^$AT3"
Range("AQ3:AQ" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C43:R" + TLR + "C43"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C43:R" + TLR + "C43"
End With
End If
End Sub

Sub AssignPtLines()
If CEctr = 7 Then ' Trend1
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R17C2:R17C3"
.Values = "=ClickChart!R17C4:R17C5"
End With
ElseIf CEctr = 9 Then ' Trend2
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R18C2:R18C3"
.Values = "=ClickChart!R18C4:R18C5"
End With
ElseIf CEctr = 11 Then ' Trend3
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R19C2:R19C3"
.Values = "=ClickChart!R19C4:R19C5"
End With
End If

If CEctr = 7 Then ' Trend1
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20)
.XValues = "=ClickChart!R17C2:R17C3" ' TRASHING HERE!!!
.Values = "=ClickChart!R17C4:R17C5"
End With
ElseIf CEctr = 9 Then ' Trend2
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) 'same for log
.XValues = "=ClickChart!R18C2:R18C3"
.Values = "=ClickChart!R18C4:R18C5"
End With
ElseIf CEctr = 11 Then ' Trend3
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' same for log
.XValues = "=ClickChart!R19C2:R19C3"
.Values = "=ClickChart!R19C4:R19C5"
End With
End If
End Sub



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 20
Default Unable to Set the XValues Property of the Series
Hello Jon!!

Thanks for responding!
<iYou didn't indicate which line failed</i

Ah, but I did....but apparently not very well :) If you look at the
2nd Sub, 2nd "If CEctr = 7" section, there's a comment that says
"TRASHING HERE". I guess it does get hidden in all those lines!

I know positively there are no blanks or errors in the data being
assigned...so that's not it. That would be too easy, right?!

I went and checked the Chart Types of those lines in both Linear and
Log Charts; they are all XY Scatter.

Thanks for the tip about "&". I didn't know that. I'll change those.

By the way, Jon....... I have you to thank for the Event class code
that let's the user click on the Price line and let's me get the info
from the line. I had no clue how to do it until I read your article
about it. Actually, it still boggles me that I got it to work 'cause I
don't really understand what I'm doing there. I'm still a beginner (at
this less than a year with formal VB training...not even a book yet) so
I feel somewhat lucky!

I really appreciate you putting your expertise out here on the web for
all to take advantage of! OK, I'll stop gushing now.........

Meanwhile, is there anything else I can tell you/show you about my
problem?

Kathy


Jon Peltier wrote:
You didn't indicate which line failed. This error message is commonly
encountered if for some reason the Line or XY chart series is not plotted,
for example if the entire range contained blanks or errors. Since the series
doesn't really exist, VBA chokes when trying to assign XValues or Values
(XValues is listed first, which is why it failed; Values would have failed
if it was first). Workarounds include making sure there's always data in
these ranges, or temporarily changing the troublesome series to another type
(area or column) before assigning XValues and Values, then changing back to
XY (or line, but you should be using XY if you're doing trends).

In the Linear window, I've got XValues, YValues, and Category (X)
Labels. In the Log window, I've only got XValues and YValues. Does
this matter?

This might mean the first type is a Line chart and the second is an XY
chart. For decent fitted lines, you should always use XY charts.

.XValues = "=ClickChart!R3C20:R" + TLR + "C20"

You should use & not + for string manipulation. The plus sign usually works,
until it doesn't.

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


"KathyC" wrote in message
oups.com...
Hello everyone..... I'm stymied by the error message and have not been
able to solve it for days. If you can, please help!

Here's the situation: I have 2 embedded charts on one worksheet.
One's linear ("ClickChartLinear") and one's log ("ClickChartLog").
Each chart has 23 series already created. The last 3--Trend1, Trend2,
and Trend3--were originally just Pt to Pt lines. The source cells
always stayed the same but the values in them could be changed by the
user. The user clicks a Trend button and clicks 2 pts on the Price
series line. The code gets the From/To dates and prices of a stock
from the clicked points and places those in the source cells. This
worked fine on both Linear & Log.

Then someone suggested I extend the line out into the future. OK, so I
let the user choose "Pt to Pt" or "Expand". Expand data is calculated
via worksheet formulas. The user goes through the clicking process,
then based on what was selected, one of 2 Subs is run to assign the
proper source data.

The Expand reassignments work fine for Log & Linear. Pt to Pt works
for Linear, but not for Log.
The code is below. Complicating matter is the fact that I created
these Trend lines a good while ago and don't remember how/why I've
ended up with different boxes in Excel's source data screen.

In the Linear window, I've got XValues, YValues, and Category (X)
Labels. In the Log window, I've only got XValues and YValues. Does
this matter?

Ex: Say my chart X axis runs from Jan 75 through Jan 11. The user
could select to draw a line from Feb 86 to Aug 90. So the Expand line
would actually go from Feb 86 to Jan 11 while the Pt to Pt line would
stop at Aug 90.

Here are the 2 Subs. The first one...no problem. Oh, and I used to
have Sub2 like
If trend 1
draw Linear
draw Log
elseif...

but decided to split it out to see if it made a difference.

If you need more info, let me know. THANK YOU SO MUCH........Kathy



Sub AssignTrendLines() ' This plots the line using 2 entered end points
and the future points
' extrapolated from using the TREND function on
the worksheet
'
If CEctr = 7 Then ' Trend1
TLR = Range("AH39").Value
Range("AO3").Value = "=INDIRECT($AG$39)*(1+$F$17)^$AR3"
Range("AO3:AO" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20) ' col t dates/ col AO
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C41:R" + TLR + "C41"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20)
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C41:R" + TLR + "C41"
End With
ElseIf CEctr = 9 Then ' Trend2
TLR = Range("AH40").Value
Range("AP3").Value = "=INDIRECT($AG$40)*(1+$F$18)^$AS3"
Range("AP3:AP" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AP
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C42:R" + TLR + "C42"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AP
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C42:R" + TLR + "C42"
End With
ElseIf CEctr = 11 Then ' Trend3
TLR = Range("AH41").Value
Range("AQ3").Value = "=INDIRECT($AG$41)*(1+$F$19)^$AT3"
Range("AQ3:AQ" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C43:R" + TLR + "C43"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C43:R" + TLR + "C43"
End With
End If
End Sub

Sub AssignPtLines()
If CEctr = 7 Then ' Trend1
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R17C2:R17C3"
.Values = "=ClickChart!R17C4:R17C5"
End With
ElseIf CEctr = 9 Then ' Trend2
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R18C2:R18C3"
.Values = "=ClickChart!R18C4:R18C5"
End With
ElseIf CEctr = 11 Then ' Trend3
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R19C2:R19C3"
.Values = "=ClickChart!R19C4:R19C5"
End With
End If

If CEctr = 7 Then ' Trend1
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20)
.XValues = "=ClickChart!R17C2:R17C3" ' TRASHING HERE!!!
.Values = "=ClickChart!R17C4:R17C5"
End With
ElseIf CEctr = 9 Then ' Trend2
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) 'same for log
.XValues = "=ClickChart!R18C2:R18C3"
.Values = "=ClickChart!R18C4:R18C5"
End With
ElseIf CEctr = 11 Then ' Trend3
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' same for log
.XValues = "=ClickChart!R19C2:R19C3"
.Values = "=ClickChart!R19C4:R19C5"
End With
End If
End Sub


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 20
Default Unable to Set the XValues Property of the Series
I give up..........Jon, if you have an answer to this I'd REALLY love
to hear it. But I've devoted several days to this one thing now and
enough is enough.

So I created a 4th trend line and let the user have 2 Pt to Pt lines
which don't get reassigned and 2 Expand into the Future Lines which do
get reassigned. This is working and people will have to lump it if
they don't like it! What the heck....this is a freebie project I'm
working on, not a job!

Still, I hate not knowing why it didn't work. But at least I can move
on to the next issue...which I'll write up if I don't find an answer
somewhere out there already.

Thanks,
Kathy


KathyC wrote:
Hello everyone..... I'm stymied by the error message and have not been
able to solve it for days. If you can, please help!

Here's the situation: I have 2 embedded charts on one worksheet.
One's linear ("ClickChartLinear") and one's log ("ClickChartLog").
Each chart has 23 series already created. The last 3--Trend1, Trend2,
and Trend3--were originally just Pt to Pt lines. The source cells
always stayed the same but the values in them could be changed by the
user. The user clicks a Trend button and clicks 2 pts on the Price
series line. The code gets the From/To dates and prices of a stock
from the clicked points and places those in the source cells. This
worked fine on both Linear & Log.

Then someone suggested I extend the line out into the future. OK, so I
let the user choose "Pt to Pt" or "Expand". Expand data is calculated
via worksheet formulas. The user goes through the clicking process,
then based on what was selected, one of 2 Subs is run to assign the
proper source data.

The Expand reassignments work fine for Log & Linear. Pt to Pt works
for Linear, but not for Log.
The code is below. Complicating matter is the fact that I created
these Trend lines a good while ago and don't remember how/why I've
ended up with different boxes in Excel's source data screen.

In the Linear window, I've got XValues, YValues, and Category (X)
Labels. In the Log window, I've only got XValues and YValues. Does
this matter?

Ex: Say my chart X axis runs from Jan 75 through Jan 11. The user
could select to draw a line from Feb 86 to Aug 90. So the Expand line
would actually go from Feb 86 to Jan 11 while the Pt to Pt line would
stop at Aug 90.

Here are the 2 Subs. The first one...no problem. Oh, and I used to
have Sub2 like
If trend 1
draw Linear
draw Log
elseif...

but decided to split it out to see if it made a difference.

If you need more info, let me know. THANK YOU SO MUCH........Kathy



Sub AssignTrendLines() ' This plots the line using 2 entered end points
and the future points
' extrapolated from using the TREND function on
the worksheet
'
If CEctr = 7 Then ' Trend1
TLR = Range("AH39").Value
Range("AO3").Value = "=INDIRECT($AG$39)*(1+$F$17)^$AR3"
Range("AO3:AO" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20) ' col t dates/ col AO
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C41:R" + TLR + "C41"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20)
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C41:R" + TLR + "C41"
End With
ElseIf CEctr = 9 Then ' Trend2
TLR = Range("AH40").Value
Range("AP3").Value = "=INDIRECT($AG$40)*(1+$F$18)^$AS3"
Range("AP3:AP" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AP
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C42:R" + TLR + "C42"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AP
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C42:R" + TLR + "C42"
End With
ElseIf CEctr = 11 Then ' Trend3
TLR = Range("AH41").Value
Range("AQ3").Value = "=INDIRECT($AG$41)*(1+$F$19)^$AT3"
Range("AQ3:AQ" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C43:R" + TLR + "C43"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C43:R" + TLR + "C43"
End With
End If
End Sub

Sub AssignPtLines()
If CEctr = 7 Then ' Trend1
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R17C2:R17C3"
.Values = "=ClickChart!R17C4:R17C5"
End With
ElseIf CEctr = 9 Then ' Trend2
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R18C2:R18C3"
.Values = "=ClickChart!R18C4:R18C5"
End With
ElseIf CEctr = 11 Then ' Trend3
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R19C2:R19C3"
.Values = "=ClickChart!R19C4:R19C5"
End With
End If

If CEctr = 7 Then ' Trend1
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20)
.XValues = "=ClickChart!R17C2:R17C3" ' TRASHING HERE!!!
.Values = "=ClickChart!R17C4:R17C5"
End With
ElseIf CEctr = 9 Then ' Trend2
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) 'same for log
.XValues = "=ClickChart!R18C2:R18C3"
.Values = "=ClickChart!R18C4:R18C5"
End With
ElseIf CEctr = 11 Then ' Trend3
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' same for log
.XValues = "=ClickChart!R19C2:R19C3"
.Values = "=ClickChart!R19C4:R19C5"
End With
End If
End Sub

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 6,582
Default Unable to Set the XValues Property of the Series
That was pretty well buried, 90% of the way to the bottom. The problems of
plain text....

It's not the data being assigned that's the problem. Excel will assign bad
data to the series. Just once bad data's been assigned, Excel can't access
further data assignments. Before you get to this step, is Series 20 visible?
Is it using good or bad data?

Try inserting these two .ChartType assignments:

With ActiveChart.SeriesCollection(20)
.ChartType = xlArea
.XValues = "=ClickChart!R17C2:R17C3"
.Values = "=ClickChart!R17C4:R17C5"
.ChartType = xlXYScatterLines
End With

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


"KathyC" wrote in message
ps.com...
Hello Jon!!

Thanks for responding!
<iYou didn't indicate which line failed</i

Ah, but I did....but apparently not very well :) If you look at the
2nd Sub, 2nd "If CEctr = 7" section, there's a comment that says
"TRASHING HERE". I guess it does get hidden in all those lines!

I know positively there are no blanks or errors in the data being
assigned...so that's not it. That would be too easy, right?!

I went and checked the Chart Types of those lines in both Linear and
Log Charts; they are all XY Scatter.

Thanks for the tip about "&". I didn't know that. I'll change those.

By the way, Jon....... I have you to thank for the Event class code
that let's the user click on the Price line and let's me get the info
from the line. I had no clue how to do it until I read your article
about it. Actually, it still boggles me that I got it to work 'cause I
don't really understand what I'm doing there. I'm still a beginner (at
this less than a year with formal VB training...not even a book yet) so
I feel somewhat lucky!

I really appreciate you putting your expertise out here on the web for
all to take advantage of! OK, I'll stop gushing now.........

Meanwhile, is there anything else I can tell you/show you about my
problem?

Kathy


Jon Peltier wrote:
You didn't indicate which line failed. This error message is commonly
encountered if for some reason the Line or XY chart series is not
plotted,
for example if the entire range contained blanks or errors. Since the
series
doesn't really exist, VBA chokes when trying to assign XValues or Values
(XValues is listed first, which is why it failed; Values would have
failed
if it was first). Workarounds include making sure there's always data in
these ranges, or temporarily changing the troublesome series to another
type
(area or column) before assigning XValues and Values, then changing back
to
XY (or line, but you should be using XY if you're doing trends).

In the Linear window, I've got XValues, YValues, and Category (X)
Labels. In the Log window, I've only got XValues and YValues. Does
this matter?

This might mean the first type is a Line chart and the second is an XY
chart. For decent fitted lines, you should always use XY charts.

.XValues = "=ClickChart!R3C20:R" + TLR + "C20"

You should use & not + for string manipulation. The plus sign usually
works,
until it doesn't.

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


"KathyC" wrote in message
oups.com...
Hello everyone..... I'm stymied by the error message and have not been
able to solve it for days. If you can, please help!

Here's the situation: I have 2 embedded charts on one worksheet.
One's linear ("ClickChartLinear") and one's log ("ClickChartLog").
Each chart has 23 series already created. The last 3--Trend1, Trend2,
and Trend3--were originally just Pt to Pt lines. The source cells
always stayed the same but the values in them could be changed by the
user. The user clicks a Trend button and clicks 2 pts on the Price
series line. The code gets the From/To dates and prices of a stock
from the clicked points and places those in the source cells. This
worked fine on both Linear & Log.

Then someone suggested I extend the line out into the future. OK, so I
let the user choose "Pt to Pt" or "Expand". Expand data is calculated
via worksheet formulas. The user goes through the clicking process,
then based on what was selected, one of 2 Subs is run to assign the
proper source data.

The Expand reassignments work fine for Log & Linear. Pt to Pt works
for Linear, but not for Log.
The code is below. Complicating matter is the fact that I created
these Trend lines a good while ago and don't remember how/why I've
ended up with different boxes in Excel's source data screen.

In the Linear window, I've got XValues, YValues, and Category (X)
Labels. In the Log window, I've only got XValues and YValues. Does
this matter?

Ex: Say my chart X axis runs from Jan 75 through Jan 11. The user
could select to draw a line from Feb 86 to Aug 90. So the Expand line
would actually go from Feb 86 to Jan 11 while the Pt to Pt line would
stop at Aug 90.

Here are the 2 Subs. The first one...no problem. Oh, and I used to
have Sub2 like
If trend 1
draw Linear
draw Log
elseif...

but decided to split it out to see if it made a difference.

If you need more info, let me know. THANK YOU SO MUCH........Kathy



Sub AssignTrendLines() ' This plots the line using 2 entered end points
and the future points
' extrapolated from using the TREND function on
the worksheet
'
If CEctr = 7 Then ' Trend1
TLR = Range("AH39").Value
Range("AO3").Value = "=INDIRECT($AG$39)*(1+$F$17)^$AR3"
Range("AO3:AO" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20) ' col t dates/ col AO
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C41:R" + TLR + "C41"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20)
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C41:R" + TLR + "C41"
End With
ElseIf CEctr = 9 Then ' Trend2
TLR = Range("AH40").Value
Range("AP3").Value = "=INDIRECT($AG$40)*(1+$F$18)^$AS3"
Range("AP3:AP" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AP
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C42:R" + TLR + "C42"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AP
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C42:R" + TLR + "C42"
End With
ElseIf CEctr = 11 Then ' Trend3
TLR = Range("AH41").Value
Range("AQ3").Value = "=INDIRECT($AG$41)*(1+$F$19)^$AT3"
Range("AQ3:AQ" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C43:R" + TLR + "C43"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C43:R" + TLR + "C43"
End With
End If
End Sub

Sub AssignPtLines()
If CEctr = 7 Then ' Trend1
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R17C2:R17C3"
.Values = "=ClickChart!R17C4:R17C5"
End With
ElseIf CEctr = 9 Then ' Trend2
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R18C2:R18C3"
.Values = "=ClickChart!R18C4:R18C5"
End With
ElseIf CEctr = 11 Then ' Trend3
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R19C2:R19C3"
.Values = "=ClickChart!R19C4:R19C5"
End With
End If

If CEctr = 7 Then ' Trend1
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20)
.XValues = "=ClickChart!R17C2:R17C3" ' TRASHING HERE!!!
.Values = "=ClickChart!R17C4:R17C5"
End With
ElseIf CEctr = 9 Then ' Trend2
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) 'same for log
.XValues = "=ClickChart!R18C2:R18C3"
.Values = "=ClickChart!R18C4:R18C5"
End With
ElseIf CEctr = 11 Then ' Trend3
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' same for log
.XValues = "=ClickChart!R19C2:R19C3"
.Values = "=ClickChart!R19C4:R19C5"
End With
End If
End Sub




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 20
Default Unable to Set the XValues Property of the Series
Hi Jon......


Right now, the data being assigned is good...... but I won't say that
bad data didn't get assigned along the way while I was writing the code
and working the bugs out. Maybe that did cause something weird.

I inserted your code but got the same error on the Xvalues line.

I appreciate your time and help very much, but don't bother with it
anymore. I've worked around it for now and hope never to come across
it again!

Kathy


Jon Peltier wrote:
That was pretty well buried, 90% of the way to the bottom. The problems of
plain text....

It's not the data being assigned that's the problem. Excel will assign bad
data to the series. Just once bad data's been assigned, Excel can't access
further data assignments. Before you get to this step, is Series 20 visible?
Is it using good or bad data?

Try inserting these two .ChartType assignments:

With ActiveChart.SeriesCollection(20)
.ChartType = xlArea
.XValues = "=ClickChart!R17C2:R17C3"
.Values = "=ClickChart!R17C4:R17C5"
.ChartType = xlXYScatterLines
End With

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


"KathyC" wrote in message
ps.com...
Hello Jon!!

Thanks for responding!
<iYou didn't indicate which line failed</i

Ah, but I did....but apparently not very well :) If you look at the
2nd Sub, 2nd "If CEctr = 7" section, there's a comment that says
"TRASHING HERE". I guess it does get hidden in all those lines!

I know positively there are no blanks or errors in the data being
assigned...so that's not it. That would be too easy, right?!

I went and checked the Chart Types of those lines in both Linear and
Log Charts; they are all XY Scatter.

Thanks for the tip about "&". I didn't know that. I'll change those.

By the way, Jon....... I have you to thank for the Event class code
that let's the user click on the Price line and let's me get the info
from the line. I had no clue how to do it until I read your article
about it. Actually, it still boggles me that I got it to work 'cause I
don't really understand what I'm doing there. I'm still a beginner (at
this less than a year with formal VB training...not even a book yet) so
I feel somewhat lucky!

I really appreciate you putting your expertise out here on the web for
all to take advantage of! OK, I'll stop gushing now.........

Meanwhile, is there anything else I can tell you/show you about my
problem?

Kathy


Jon Peltier wrote:
You didn't indicate which line failed. This error message is commonly
encountered if for some reason the Line or XY chart series is not
plotted,
for example if the entire range contained blanks or errors. Since the
series
doesn't really exist, VBA chokes when trying to assign XValues or Values
(XValues is listed first, which is why it failed; Values would have
failed
if it was first). Workarounds include making sure there's always data in
these ranges, or temporarily changing the troublesome series to another
type
(area or column) before assigning XValues and Values, then changing back
to
XY (or line, but you should be using XY if you're doing trends).

In the Linear window, I've got XValues, YValues, and Category (X)
Labels. In the Log window, I've only got XValues and YValues. Does
this matter?

This might mean the first type is a Line chart and the second is an XY
chart. For decent fitted lines, you should always use XY charts.

.XValues = "=ClickChart!R3C20:R" + TLR + "C20"

You should use & not + for string manipulation. The plus sign usually
works,
until it doesn't.

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


"KathyC" wrote in message
oups.com...
Hello everyone..... I'm stymied by the error message and have not been
able to solve it for days. If you can, please help!

Here's the situation: I have 2 embedded charts on one worksheet.
One's linear ("ClickChartLinear") and one's log ("ClickChartLog").
Each chart has 23 series already created. The last 3--Trend1, Trend2,
and Trend3--were originally just Pt to Pt lines. The source cells
always stayed the same but the values in them could be changed by the
user. The user clicks a Trend button and clicks 2 pts on the Price
series line. The code gets the From/To dates and prices of a stock
from the clicked points and places those in the source cells. This
worked fine on both Linear & Log.

Then someone suggested I extend the line out into the future. OK, so I
let the user choose "Pt to Pt" or "Expand". Expand data is calculated
via worksheet formulas. The user goes through the clicking process,
then based on what was selected, one of 2 Subs is run to assign the
proper source data.

The Expand reassignments work fine for Log & Linear. Pt to Pt works
for Linear, but not for Log.
The code is below. Complicating matter is the fact that I created
these Trend lines a good while ago and don't remember how/why I've
ended up with different boxes in Excel's source data screen.

In the Linear window, I've got XValues, YValues, and Category (X)
Labels. In the Log window, I've only got XValues and YValues. Does
this matter?

Ex: Say my chart X axis runs from Jan 75 through Jan 11. The user
could select to draw a line from Feb 86 to Aug 90. So the Expand line
would actually go from Feb 86 to Jan 11 while the Pt to Pt line would
stop at Aug 90.

Here are the 2 Subs. The first one...no problem. Oh, and I used to
have Sub2 like
If trend 1
draw Linear
draw Log
elseif...

but decided to split it out to see if it made a difference.

If you need more info, let me know. THANK YOU SO MUCH........Kathy



Sub AssignTrendLines() ' This plots the line using 2 entered end points
and the future points
' extrapolated from using the TREND function on
the worksheet
'
If CEctr = 7 Then ' Trend1
TLR = Range("AH39").Value
Range("AO3").Value = "=INDIRECT($AG$39)*(1+$F$17)^$AR3"
Range("AO3:AO" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20) ' col t dates/ col AO
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C41:R" + TLR + "C41"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20)
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C41:R" + TLR + "C41"
End With
ElseIf CEctr = 9 Then ' Trend2
TLR = Range("AH40").Value
Range("AP3").Value = "=INDIRECT($AG$40)*(1+$F$18)^$AS3"
Range("AP3:AP" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AP
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C42:R" + TLR + "C42"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AP
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C42:R" + TLR + "C42"
End With
ElseIf CEctr = 11 Then ' Trend3
TLR = Range("AH41").Value
Range("AQ3").Value = "=INDIRECT($AG$41)*(1+$F$19)^$AT3"
Range("AQ3:AQ" & TLR).Select
Selection.FillDown
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C43:R" + TLR + "C43"
End With
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R3C20:R" + TLR + "C20"
.Values = "=ClickChart!R3C43:R" + TLR + "C43"
End With
End If
End Sub

Sub AssignPtLines()
If CEctr = 7 Then ' Trend1
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R17C2:R17C3"
.Values = "=ClickChart!R17C4:R17C5"
End With
ElseIf CEctr = 9 Then ' Trend2
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R18C2:R18C3"
.Values = "=ClickChart!R18C4:R18C5"
End With
ElseIf CEctr = 11 Then ' Trend3
ActiveSheet.ChartObjects("ClickChartLinear").Activ ate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' col t dates/ col AQ
prices
.XValues = "=ClickChart!R19C2:R19C3"
.Values = "=ClickChart!R19C4:R19C5"
End With
End If

If CEctr = 7 Then ' Trend1
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(20)
.XValues = "=ClickChart!R17C2:R17C3" ' TRASHING HERE!!!
.Values = "=ClickChart!R17C4:R17C5"
End With
ElseIf CEctr = 9 Then ' Trend2
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(21) 'same for log
.XValues = "=ClickChart!R18C2:R18C3"
.Values = "=ClickChart!R18C4:R18C5"
End With
ElseIf CEctr = 11 Then ' Trend3
ActiveSheet.ChartObjects("ClickChartLog").Activate
ActiveChart.PlotArea.Select
With ActiveChart.SeriesCollection(22) ' same for log
.XValues = "=ClickChart!R19C2:R19C3"
.Values = "=ClickChart!R19C4:R19C5"
End With
End If
End Sub



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to set the XValues property of the Series class Ben Charts and Charting in Excel 7 December 7th 06 10:01 PM
Unable to set the Values property of the Series class rafael garcia Charts and Charting in Excel 1 September 25th 06 04:31 PM
Unable to set the formula property of the series class Malhar Charts and Charting in Excel 1 July 24th 06 05:50 PM
Unable to set the XValues property of the Series class ramkumar_cpt Charts and Charting in Excel 5 November 29th 05 02:13 PM
VBA error: Unable to set the Values property of the Series class Marco Shaw Charts and Charting in Excel 1 July 12th 05 02:34 PM


All times are GMT +1. The time now is 05:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement