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