Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem programming .SeriesCollection().XValues and .Values
Hi everyone. I am having a difficult time programmatically assigning a named
range using the method SeriesCollection().XValue and SeriesCollection().Value. I keep getting the following error message, "Run time error '1004' Unable to set the Values property of the Series class. I am fairly novice VBA programmer, mostly self taught, I have learned signigicantly over the last few months but cannot overcome this one error. I have tried to emulate the code written by Jon Peltier in his VBA and Chart primer, from his website. my code is below, Sub graphIt(x1Axis As String, y1Axis As String, x_EST As String, y_EST As String, curve As String) Dim objWorkSheet As Object Dim Y_data_range As String Dim X_data_range As String Dim Y_est_data_range As String Dim X_est_data_range As String Dim fittedCurve As String Dim fuelCurve As ChartObject Set Y_data_range = OLEObject.Sheet(Sheet2).Range(y1Axis) X_data_range = x1Axis Y_est_data_range = y_EST X_est_data_range = x_EST fittedCurve = curve Set fuelCurve = ActiveSheet.ChartObjects.Add _ (Left:=500, Width:=750, Top:=150, Height:=450) With fuelCurve.Chart 'make smoth XY Scatter Chart .chartType = xlXYScatterSmooth ' ' add series from selected name ranges .SeriesCollection(1).Name = "Data" (ERROR occurs here) .SeriesCollection(1).XValues = "=(Sheet2!)" & X_data_range .SeriesCollection(1).Values = "=" (Sheet2!)" & Y_data_range .SeriesCollection(2).Name = "Estimated" .SeriesCollection(2).XValues = "=(Sheet2!)" & X_est_data_range .SeriesCollection(2).Values = "=(Sheet2!)" & Y_est_data_range End With End Sub Regards, Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem programming .SeriesCollection().XValues and .Values
'make smoth XY Scatter Chart
.chartType = xlXYScatterSmooth ' ' add series from selected name ranges .SeriesCollection(1).Name = "Data" (ERROR occurs here) At this point your chart doesn't have any series, try With .SeriesCollection.NewSeries .Name = "Data" ' etc end with And similar for subsequent series. Or, Dim sr as Series ' code Set sr =.SeriesCollection.NewSeries and work with 'sr' In passing, this can't be right - Dim Y_data_range As String Set Y_data_range = OLEObject.Sheet(Sheet2).Range(y1Axis) Regards, Peter T "Greg Fish" wrote in message ... Hi everyone. I am having a difficult time programmatically assigning a named range using the method SeriesCollection().XValue and SeriesCollection().Value. I keep getting the following error message, "Run time error '1004' Unable to set the Values property of the Series class. I am fairly novice VBA programmer, mostly self taught, I have learned signigicantly over the last few months but cannot overcome this one error. I have tried to emulate the code written by Jon Peltier in his VBA and Chart primer, from his website. my code is below, Sub graphIt(x1Axis As String, y1Axis As String, x_EST As String, y_EST As String, curve As String) Dim objWorkSheet As Object Dim Y_data_range As String Dim X_data_range As String Dim Y_est_data_range As String Dim X_est_data_range As String Dim fittedCurve As String Dim fuelCurve As ChartObject Set Y_data_range = OLEObject.Sheet(Sheet2).Range(y1Axis) X_data_range = x1Axis Y_est_data_range = y_EST X_est_data_range = x_EST fittedCurve = curve Set fuelCurve = ActiveSheet.ChartObjects.Add _ (Left:=500, Width:=750, Top:=150, Height:=450) With fuelCurve.Chart 'make smoth XY Scatter Chart .chartType = xlXYScatterSmooth ' ' add series from selected name ranges .SeriesCollection(1).Name = "Data" (ERROR occurs here) .SeriesCollection(1).XValues = "=(Sheet2!)" & X_data_range .SeriesCollection(1).Values = "=" (Sheet2!)" & Y_data_range .SeriesCollection(2).Name = "Estimated" .SeriesCollection(2).XValues = "=(Sheet2!)" & X_est_data_range .SeriesCollection(2).Values = "=(Sheet2!)" & Y_est_data_range End With End Sub Regards, Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem programming .SeriesCollection().XValues and .Values
Peter T -
I followed your suggestion as shown Sub graphIt(x1Axis As String, y1Axis As String, x_EST As String, y_EST As String, curve As String) Dim Y_data_range As String Dim X_data_range As String Dim Y_est_data_range As String Dim X_est_data_range As String Dim fittedCurve As String Dim fuelCurve As ChartObject Y_data_range = y1Axis X_data_range = x1Axis Y_est_data_range = y_EST X_est_data_range = x_EST fittedCurve = curve Set fuelCurve = ActiveSheet.ChartObjects.Add _ (Left:=500, Width:=750, Top:=150, Height:=450) With fuelCurve.Chart 'make smoth XY Scatter Chart .chartType = xlXYScatterSmooth ' ' add series from selected name ranges With .SeriesCollection.NewSeries .Name = "Data" .XValues = "=" & X_data_range LIND OF CODE ABOVE TRIGGERS RUN TIME ERROR UNABLE TO SET THE XVALUES PROPERTY OF THE SERIES CLASS (note used all CAPS to distinguish text) .Values = "=" & Y_data_range End With With .SeriesCollection.NewSeries .Name = "Estimated" .XValues = "=" & X_est_data_range .Values = "=" & Y_est_data_range End With End With End Sub With recommended changes still getting an error, that I was having before. I have a named range, which I am basically passing as a string argument to the function, this seems pretty straightforward, since the string should just serve as an argument for the function call, in this case XValues. Thanks for your help. Greg "Peter T" wrote: 'make smoth XY Scatter Chart .chartType = xlXYScatterSmooth ' ' add series from selected name ranges .SeriesCollection(1).Name = "Data" (ERROR occurs here) At this point your chart doesn't have any series, try With .SeriesCollection.NewSeries .Name = "Data" ' etc end with And similar for subsequent series. Or, Dim sr as Series ' code Set sr =.SeriesCollection.NewSeries and work with 'sr' In passing, this can't be right - Dim Y_data_range As String Set Y_data_range = OLEObject.Sheet(Sheet2).Range(y1Axis) Regards, Peter T "Greg Fish" wrote in message ... Hi everyone. I am having a difficult time programmatically assigning a named range using the method SeriesCollection().XValue and SeriesCollection().Value. I keep getting the following error message, "Run time error '1004' Unable to set the Values property of the Series class. I am fairly novice VBA programmer, mostly self taught, I have learned signigicantly over the last few months but cannot overcome this one error. I have tried to emulate the code written by Jon Peltier in his VBA and Chart primer, from his website. my code is below, Sub graphIt(x1Axis As String, y1Axis As String, x_EST As String, y_EST As String, curve As String) Dim objWorkSheet As Object Dim Y_data_range As String Dim X_data_range As String Dim Y_est_data_range As String Dim X_est_data_range As String Dim fittedCurve As String Dim fuelCurve As ChartObject Set Y_data_range = OLEObject.Sheet(Sheet2).Range(y1Axis) X_data_range = x1Axis Y_est_data_range = y_EST X_est_data_range = x_EST fittedCurve = curve Set fuelCurve = ActiveSheet.ChartObjects.Add _ (Left:=500, Width:=750, Top:=150, Height:=450) With fuelCurve.Chart 'make smoth XY Scatter Chart .chartType = xlXYScatterSmooth ' ' add series from selected name ranges .SeriesCollection(1).Name = "Data" (ERROR occurs here) .SeriesCollection(1).XValues = "=(Sheet2!)" & X_data_range .SeriesCollection(1).Values = "=" (Sheet2!)" & Y_data_range .SeriesCollection(2).Name = "Estimated" .SeriesCollection(2).XValues = "=(Sheet2!)" & X_est_data_range .SeriesCollection(2).Values = "=(Sheet2!)" & Y_est_data_range End With End Sub Regards, Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem programming .SeriesCollection().XValues and .Values
After taking in the advice and pouring over this forum, not to mention
several Google searches have rewritten the Sub routine as follows: Sub graphIt(x1Axis As String, y1Axis As String, x_EST As String, y_EST As String, curve As String) Dim objWorkSheet As Object Dim Y_data_range, X_data_range As Range Dim Y_est_data_range, X_est_data_range As Range Dim sY_data_range As String Dim sX_data_range As String Dim sY_est_data_range As String Dim sX_est_data_range As String Dim fittedCurve As String Dim fuelCurve As ChartObject Worksheets(2).Activate Set Y_data_range = ActiveSheet.Range(y1Axis) Set X_data_range = ActiveSheet.Range(x1Axis) Set Y_est_data_range = ActiveSheet.Range(y_EST) Set X_est_data_range = ActiveSheet.Range(x_EST) sY_data_range = "'" & ActiveSheet.Name & "'!" & Y_data_range.Address sX_data_range = "'" & ActiveSheet.Name & "'!" & X_data_range.Address sY_est_data_range = "'" & ActiveSheet.Name & "'!" & Y_est_data_range.Address sX_est_data_range = "'" & ActiveSheet.Name & "'!" & X_est_data_range.Address fittedCurve = curve Worksheets(1).Activate Set fuelCurve = ActiveSheet.ChartObjects.Add _ (Left:=500, Width:=750, Top:=150, Height:=450) With fuelCurve.Chart 'make smoth XY Scatter Chart .chartType = xlXYScatterSmooth ' ' add series from selected name ranges With .SeriesCollection.NewSeries .XValues = "=(" & sX_data_range & ")" STILL ERRORING OUT .Values = "=(" & sY_data_range & ")" .Name = "Data" End With With .SeriesCollection.NewSeries .Name = "Estimated" .XValues = "=" & X_est_data_range .Values = "=" & Y_est_data_range End With End With End Sub The annoying part is, still getting the same error message. Absolutely frustrating especially when I KNOW that the value for sX_data_range is in fact the correct data, and address for the named range. This could not be anymore frustrating, not to mention having a pending deadline. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem programming .SeriesCollection().XValues and .Values
from your slightly earlier post
.XValues = "=" & X_data_range LIND OF CODE ABOVE TRIGGERS RUN TIME ERROR UNABLE TO SET THE XVALUES PROPERTY OF THE SERIES CLASS and below .XValues = "=(" & sX_data_range & ")" STILL ERRORING OUT You don't want those brackets but - Change the range address to R1C1 style, eg s = "='" & ActiveWorkbook.Name & "'!" s = s & myRange.Address(ReferenceStyle:=xlR1C1) ' or .Address(, , xlR1C1) mySeries.Values = s ' or .XValues = s Regards, Peter T "Greg Fish" wrote in message ... After taking in the advice and pouring over this forum, not to mention several Google searches have rewritten the Sub routine as follows: Sub graphIt(x1Axis As String, y1Axis As String, x_EST As String, y_EST As String, curve As String) Dim objWorkSheet As Object Dim Y_data_range, X_data_range As Range Dim Y_est_data_range, X_est_data_range As Range Dim sY_data_range As String Dim sX_data_range As String Dim sY_est_data_range As String Dim sX_est_data_range As String Dim fittedCurve As String Dim fuelCurve As ChartObject Worksheets(2).Activate Set Y_data_range = ActiveSheet.Range(y1Axis) Set X_data_range = ActiveSheet.Range(x1Axis) Set Y_est_data_range = ActiveSheet.Range(y_EST) Set X_est_data_range = ActiveSheet.Range(x_EST) sY_data_range = "'" & ActiveSheet.Name & "'!" & Y_data_range.Address sX_data_range = "'" & ActiveSheet.Name & "'!" & X_data_range.Address sY_est_data_range = "'" & ActiveSheet.Name & "'!" & Y_est_data_range.Address sX_est_data_range = "'" & ActiveSheet.Name & "'!" & X_est_data_range.Address fittedCurve = curve Worksheets(1).Activate Set fuelCurve = ActiveSheet.ChartObjects.Add _ (Left:=500, Width:=750, Top:=150, Height:=450) With fuelCurve.Chart 'make smoth XY Scatter Chart .chartType = xlXYScatterSmooth ' ' add series from selected name ranges With .SeriesCollection.NewSeries .XValues = "=(" & sX_data_range & ")" STILL ERRORING OUT .Values = "=(" & sY_data_range & ")" .Name = "Data" End With With .SeriesCollection.NewSeries .Name = "Estimated" .XValues = "=" & X_est_data_range .Values = "=" & Y_est_data_range End With End With End Sub The annoying part is, still getting the same error message. Absolutely frustrating especially when I KNOW that the value for sX_data_range is in fact the correct data, and address for the named range. This could not be anymore frustrating, not to mention having a pending deadline. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem programming .SeriesCollection().XValues and .Values
Okay I am confused now, so what is the point of using the named range, if I
am just going to end up putting in the R1C1 style reference? This just seems inordinately difficult. I am not sure if I follow your syntax s = s & myRange.Address(ReferenceStyle:=xlR1C1) ' or .Address(, , xlR1C1) mySeries.Values = s ' or .XValues = s Forgive my exasperation, but this has been far more difficult than I ever imagined, and I am rapidly running out of time. "Peter T" wrote: from your slightly earlier post .XValues = "=" & X_data_range LIND OF CODE ABOVE TRIGGERS RUN TIME ERROR UNABLE TO SET THE XVALUES PROPERTY OF THE SERIES CLASS and below .XValues = "=(" & sX_data_range & ")" STILL ERRORING OUT You don't want those brackets but - Change the range address to R1C1 style, eg s = "='" & ActiveWorkbook.Name & "'!" s = s & myRange.Address(ReferenceStyle:=xlR1C1) ' or .Address(, , xlR1C1) mySeries.Values = s ' or .XValues = s Regards, Peter T "Greg Fish" wrote in message ... After taking in the advice and pouring over this forum, not to mention several Google searches have rewritten the Sub routine as follows: Sub graphIt(x1Axis As String, y1Axis As String, x_EST As String, y_EST As String, curve As String) Dim objWorkSheet As Object Dim Y_data_range, X_data_range As Range Dim Y_est_data_range, X_est_data_range As Range Dim sY_data_range As String Dim sX_data_range As String Dim sY_est_data_range As String Dim sX_est_data_range As String Dim fittedCurve As String Dim fuelCurve As ChartObject Worksheets(2).Activate Set Y_data_range = ActiveSheet.Range(y1Axis) Set X_data_range = ActiveSheet.Range(x1Axis) Set Y_est_data_range = ActiveSheet.Range(y_EST) Set X_est_data_range = ActiveSheet.Range(x_EST) sY_data_range = "'" & ActiveSheet.Name & "'!" & Y_data_range.Address sX_data_range = "'" & ActiveSheet.Name & "'!" & X_data_range.Address sY_est_data_range = "'" & ActiveSheet.Name & "'!" & Y_est_data_range.Address sX_est_data_range = "'" & ActiveSheet.Name & "'!" & X_est_data_range.Address fittedCurve = curve Worksheets(1).Activate Set fuelCurve = ActiveSheet.ChartObjects.Add _ (Left:=500, Width:=750, Top:=150, Height:=450) With fuelCurve.Chart 'make smoth XY Scatter Chart .chartType = xlXYScatterSmooth ' ' add series from selected name ranges With .SeriesCollection.NewSeries .XValues = "=(" & sX_data_range & ")" STILL ERRORING OUT .Values = "=(" & sY_data_range & ")" .Name = "Data" End With With .SeriesCollection.NewSeries .Name = "Estimated" .XValues = "=" & X_est_data_range .Values = "=" & Y_est_data_range End With End With End Sub The annoying part is, still getting the same error message. Absolutely frustrating especially when I KNOW that the value for sX_data_range is in fact the correct data, and address for the named range. This could not be anymore frustrating, not to mention having a pending deadline. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem programming .SeriesCollection().XValues and .Values
Peter T -
First off I want to thank you mightily, you pointed me in the right direction, I changed part of the code to the following, and now it works Set Y_data_range = ActiveSheet.Range(y1Axis) Set X_data_range = ActiveSheet.Range(x1Axis) Set Y_est_data_range = ActiveSheet.Range(y_EST) Set X_est_data_range = ActiveSheet.Range(x_EST) sY_data_range = "'" & ActiveSheet.Name & "'!" & Y_data_range.Address(ReferenceStyle:=xlR1C1) sX_data_range = "'" & ActiveSheet.Name & "'!" & X_data_range.Address(ReferenceStyle:=xlR1C1) sY_est_data_range = "'" & ActiveSheet.Name & "'!" & Y_est_data_range.Address(ReferenceStyle:=xlR1C1) sX_est_data_range = "'" & ActiveSheet.Name & "'!" & X_est_data_range.Address(ReferenceStyle:=xlR1C1) I cannot begin to thank you enough. That was extremely bedeviling error to get through. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem programming .SeriesCollection().XValues and .Values
I'm confused too, your code appeared to be applying an address not a name,
hence my example was loosly based on correcting (way below) - sY_data_range = "'" & ActiveSheet.Name & "'!" & Y_data_range.Address This should work with for a named range - s = "='" & ActiveWorkbook.Name & "'!" & "myName mySeries.Values = s ' or .XValues = s I confess though I may not have read your code fully. However in your slightly later post all appears to be working, great <g Regards, Peter T "Greg Fish" wrote in message ... Okay I am confused now, so what is the point of using the named range, if I am just going to end up putting in the R1C1 style reference? This just seems inordinately difficult. I am not sure if I follow your syntax s = s & myRange.Address(ReferenceStyle:=xlR1C1) ' or .Address(, , xlR1C1) mySeries.Values = s ' or .XValues = s Forgive my exasperation, but this has been far more difficult than I ever imagined, and I am rapidly running out of time. "Peter T" wrote: from your slightly earlier post .XValues = "=" & X_data_range LIND OF CODE ABOVE TRIGGERS RUN TIME ERROR UNABLE TO SET THE XVALUES PROPERTY OF THE SERIES CLASS and below .XValues = "=(" & sX_data_range & ")" STILL ERRORING OUT You don't want those brackets but - Change the range address to R1C1 style, eg s = "='" & ActiveWorkbook.Name & "'!" s = s & myRange.Address(ReferenceStyle:=xlR1C1) ' or .Address(, , xlR1C1) mySeries.Values = s ' or .XValues = s Regards, Peter T "Greg Fish" wrote in message ... After taking in the advice and pouring over this forum, not to mention several Google searches have rewritten the Sub routine as follows: Sub graphIt(x1Axis As String, y1Axis As String, x_EST As String, y_EST As String, curve As String) Dim objWorkSheet As Object Dim Y_data_range, X_data_range As Range Dim Y_est_data_range, X_est_data_range As Range Dim sY_data_range As String Dim sX_data_range As String Dim sY_est_data_range As String Dim sX_est_data_range As String Dim fittedCurve As String Dim fuelCurve As ChartObject Worksheets(2).Activate Set Y_data_range = ActiveSheet.Range(y1Axis) Set X_data_range = ActiveSheet.Range(x1Axis) Set Y_est_data_range = ActiveSheet.Range(y_EST) Set X_est_data_range = ActiveSheet.Range(x_EST) sY_data_range = "'" & ActiveSheet.Name & "'!" & Y_data_range.Address sX_data_range = "'" & ActiveSheet.Name & "'!" & X_data_range.Address sY_est_data_range = "'" & ActiveSheet.Name & "'!" & Y_est_data_range.Address sX_est_data_range = "'" & ActiveSheet.Name & "'!" & X_est_data_range.Address fittedCurve = curve Worksheets(1).Activate Set fuelCurve = ActiveSheet.ChartObjects.Add _ (Left:=500, Width:=750, Top:=150, Height:=450) With fuelCurve.Chart 'make smoth XY Scatter Chart .chartType = xlXYScatterSmooth ' ' add series from selected name ranges With .SeriesCollection.NewSeries .XValues = "=(" & sX_data_range & ")" STILL ERRORING OUT .Values = "=(" & sY_data_range & ")" .Name = "Data" End With With .SeriesCollection.NewSeries .Name = "Estimated" .XValues = "=" & X_est_data_range .Values = "=" & Y_est_data_range End With End With End Sub The annoying part is, still getting the same error message. Absolutely frustrating especially when I KNOW that the value for sX_data_range is in fact the correct data, and address for the named range. This could not be anymore frustrating, not to mention having a pending deadline. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limit on array when using seriescollection.XValues and .Values met | Excel Programming | |||
SeriesCollection(i) and XValues= Array() error | Excel Programming | |||
Can't set Chart's SeriesCollection XValues | Excel Programming | |||
Problem with plotting a chart when using arrays as Values and Xvalues | Charts and Charting in Excel | |||
Problem with setting values for SeriesCollection | Excel Programming |