Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to set the XValues property of the Series class | Charts and Charting in Excel | |||
Unable to set the Values property of the Series class | Charts and Charting in Excel | |||
Unable to set the formula property of the series class | Charts and Charting in Excel | |||
Unable to set the XValues property of the Series class | Charts and Charting in Excel | |||
VBA error: Unable to set the Values property of the Series class | Charts and Charting in Excel |