Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming Chart Series
Can someone help with the code snipet below. I am trying to program a chart
series but keep getting errors. Thanks, Ken Dim oSheet As Worksheet Dim oChartObj As ChartObject Dim Series As Series Set oSheet = ThisWorkbook.Worksheets(WorkSheetName) RowNo = 43 Set oChartObj = oSheet.ChartObjects("Chart 1") With oChartObj .Top = oSheet.Rows(RowNo + 1).Top .Left = oSheet.Columns("E").Left .Width = oSheet.Columns("R").Left - oSheet.Columns("E").Left .Height = oSheet.Rows(RowNo + 29).Top - oSheet.Rows(RowNo + 1).Top End With ' Location Reports With oChart.Chart.SeriesCollection(1) ' "Location Reports") .XValues = "='17AE'!R3C13:R42C13" .Values = Worksheets(oSheet.Name).Range(Cells(3, Col_N), Cells(RowNo, Col_N)) End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming Chart Series
The following assessment was very hasty. The appended code appears to work.
Your code didn't give Col_N a value (therefore, default = 0), and, although not essential, you didn't declare either RowNo or Col_N. Also, I believe you intended to use oChartObj instead of oChart in the following line: With oChart.Chart.SeriesCollection(1) I believe the above should be: With oChartObj.Chart.Series(1) The expression Worksheets(oSheet.Name) returns a worksheet object. However, you already defined the variable oSheet as refering to a worksheet. So, you need only use oSheet in place of Worksheets(oSheet.Name). Unqualified ranges default to the active sheet. In the following line, if oSheet isn't the active sheet then the Cells method needs to be qualified: Worksheets(oSheet.Name).Range(Cells(3, Col_N), Cells(RowNo, Col_N)) Using ws in place of oSheet, I believe the above should have been: ws.Range(ws.Cells(3, Col_N), ws.Cells(RowNo, Col_N)) I substituted ws for oSheet in the below code to conserve space and hopefully avoid workwrap. Dim ws As Worksheet Dim oChartObj As ChartObject Dim RowNo As Long, Col_N As Long Set ws = ThisWorkbook.Worksheets("Sheet1") RowNo = 43: Col_N = 3 'change value to suit Set oChartObj = ws.ChartObjects("Chart 1") With oChartObj .Top = ws.Rows(RowNo + 1).Top .Left = ws.Columns("E").Left .Width = ws.Columns("R").Left - ws.Columns("E").Left .Height = ws.Rows(RowNo + 29).Top - ws.Rows(RowNo + 1).Top With .Chart.SeriesCollection(1) .XValues = "='17AE'!R3C13:R42C13" .Values = ws.Range(ws.Cells(3, Col_N), ws.Cells(RowNo, Col_N)) End With End With End Sub Regards, Greg "Ken Kazinski" wrote: Can someone help with the code snipet below. I am trying to program a chart series but keep getting errors. Thanks, Ken Dim oSheet As Worksheet Dim oChartObj As ChartObject Dim Series As Series Set oSheet = ThisWorkbook.Worksheets(WorkSheetName) RowNo = 43 Set oChartObj = oSheet.ChartObjects("Chart 1") With oChartObj .Top = oSheet.Rows(RowNo + 1).Top .Left = oSheet.Columns("E").Left .Width = oSheet.Columns("R").Left - oSheet.Columns("E").Left .Height = oSheet.Rows(RowNo + 29).Top - oSheet.Rows(RowNo + 1).Top End With ' Location Reports With oChart.Chart.SeriesCollection(1) ' "Location Reports") .XValues = "='17AE'!R3C13:R42C13" .Values = Worksheets(oSheet.Name).Range(Cells(3, Col_N), Cells(RowNo, Col_N)) End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming Chart Series
Hi Greg,
Thanks for the help but I still get error 1004: Unable to set xvalues of the series. I copied the code but still I am getting errors. I am using excel 2003 SP1. Thanks, Ken "Greg Wilson" wrote: The following assessment was very hasty. The appended code appears to work. Your code didn't give Col_N a value (therefore, default = 0), and, although not essential, you didn't declare either RowNo or Col_N. Also, I believe you intended to use oChartObj instead of oChart in the following line: With oChart.Chart.SeriesCollection(1) I believe the above should be: With oChartObj.Chart.Series(1) The expression Worksheets(oSheet.Name) returns a worksheet object. However, you already defined the variable oSheet as refering to a worksheet. So, you need only use oSheet in place of Worksheets(oSheet.Name). Unqualified ranges default to the active sheet. In the following line, if oSheet isn't the active sheet then the Cells method needs to be qualified: Worksheets(oSheet.Name).Range(Cells(3, Col_N), Cells(RowNo, Col_N)) Using ws in place of oSheet, I believe the above should have been: ws.Range(ws.Cells(3, Col_N), ws.Cells(RowNo, Col_N)) I substituted ws for oSheet in the below code to conserve space and hopefully avoid workwrap. Dim ws As Worksheet Dim oChartObj As ChartObject Dim RowNo As Long, Col_N As Long Set ws = ThisWorkbook.Worksheets("Sheet1") RowNo = 43: Col_N = 3 'change value to suit Set oChartObj = ws.ChartObjects("Chart 1") With oChartObj .Top = ws.Rows(RowNo + 1).Top .Left = ws.Columns("E").Left .Width = ws.Columns("R").Left - ws.Columns("E").Left .Height = ws.Rows(RowNo + 29).Top - ws.Rows(RowNo + 1).Top With .Chart.SeriesCollection(1) .XValues = "='17AE'!R3C13:R42C13" .Values = ws.Range(ws.Cells(3, Col_N), ws.Cells(RowNo, Col_N)) End With End With End Sub Regards, Greg "Ken Kazinski" wrote: Can someone help with the code snipet below. I am trying to program a chart series but keep getting errors. Thanks, Ken Dim oSheet As Worksheet Dim oChartObj As ChartObject Dim Series As Series Set oSheet = ThisWorkbook.Worksheets(WorkSheetName) RowNo = 43 Set oChartObj = oSheet.ChartObjects("Chart 1") With oChartObj .Top = oSheet.Rows(RowNo + 1).Top .Left = oSheet.Columns("E").Left .Width = oSheet.Columns("R").Left - oSheet.Columns("E").Left .Height = oSheet.Rows(RowNo + 29).Top - oSheet.Rows(RowNo + 1).Top End With ' Location Reports With oChart.Chart.SeriesCollection(1) ' "Location Reports") .XValues = "='17AE'!R3C13:R42C13" .Values = Worksheets(oSheet.Name).Range(Cells(3, Col_N), Cells(RowNo, Col_N)) End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming Chart Series
1. Is the worksheet protected? I get the error you describe in this case.
2. Is the spelling of sheet "17AE" correct? Perhaps there is a space in the name not included in the formula? I get this error in this case. 3. Note that your code sets RowNo to 43 and therefore sets the Y-values to 41 data points as implied by: Worksheets(oSheet.Name).Range(Cells(3, Col_N), Cells(RowNo, Col_N)) Your code also sets the X-Values to 42 data points with the formula: "='17AE'!R3C13:R42C13" This doesn't cause me the error you describe but may for you. 4. Your code implies that your chart is an XY-Scatter. If not, it doesn't cause me the error you describe but may for you. Regards, Greg "Ken Kazinski" wrote: Hi Greg, Thanks for the help but I still get error 1004: Unable to set xvalues of the series. I copied the code but still I am getting errors. I am using excel 2003 SP1. Thanks, Ken "Greg Wilson" wrote: The following assessment was very hasty. The appended code appears to work. Your code didn't give Col_N a value (therefore, default = 0), and, although not essential, you didn't declare either RowNo or Col_N. Also, I believe you intended to use oChartObj instead of oChart in the following line: With oChart.Chart.SeriesCollection(1) I believe the above should be: With oChartObj.Chart.Series(1) The expression Worksheets(oSheet.Name) returns a worksheet object. However, you already defined the variable oSheet as refering to a worksheet. So, you need only use oSheet in place of Worksheets(oSheet.Name). Unqualified ranges default to the active sheet. In the following line, if oSheet isn't the active sheet then the Cells method needs to be qualified: Worksheets(oSheet.Name).Range(Cells(3, Col_N), Cells(RowNo, Col_N)) Using ws in place of oSheet, I believe the above should have been: ws.Range(ws.Cells(3, Col_N), ws.Cells(RowNo, Col_N)) I substituted ws for oSheet in the below code to conserve space and hopefully avoid workwrap. Dim ws As Worksheet Dim oChartObj As ChartObject Dim RowNo As Long, Col_N As Long Set ws = ThisWorkbook.Worksheets("Sheet1") RowNo = 43: Col_N = 3 'change value to suit Set oChartObj = ws.ChartObjects("Chart 1") With oChartObj .Top = ws.Rows(RowNo + 1).Top .Left = ws.Columns("E").Left .Width = ws.Columns("R").Left - ws.Columns("E").Left .Height = ws.Rows(RowNo + 29).Top - ws.Rows(RowNo + 1).Top With .Chart.SeriesCollection(1) .XValues = "='17AE'!R3C13:R42C13" .Values = ws.Range(ws.Cells(3, Col_N), ws.Cells(RowNo, Col_N)) End With End With End Sub Regards, Greg "Ken Kazinski" wrote: Can someone help with the code snipet below. I am trying to program a chart series but keep getting errors. Thanks, Ken Dim oSheet As Worksheet Dim oChartObj As ChartObject Dim Series As Series Set oSheet = ThisWorkbook.Worksheets(WorkSheetName) RowNo = 43 Set oChartObj = oSheet.ChartObjects("Chart 1") With oChartObj .Top = oSheet.Rows(RowNo + 1).Top .Left = oSheet.Columns("E").Left .Width = oSheet.Columns("R").Left - oSheet.Columns("E").Left .Height = oSheet.Rows(RowNo + 29).Top - oSheet.Rows(RowNo + 1).Top End With ' Location Reports With oChart.Chart.SeriesCollection(1) ' "Location Reports") .XValues = "='17AE'!R3C13:R42C13" .Values = Worksheets(oSheet.Name).Range(Cells(3, Col_N), Cells(RowNo, Col_N)) End With |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming Chart Series
Correction to point 3. Should have said:
Your code also sets the X-Values to 40 data points with the formula: "='17AE'!R3C13:R42C13" The above conflicts with the 41 data points for the X-Values. Regards, Greg "Greg Wilson" wrote: 1. Is the worksheet protected? I get the error you describe in this case. 2. Is the spelling of sheet "17AE" correct? Perhaps there is a space in the name not included in the formula? I get this error in this case. 3. Note that your code sets RowNo to 43 and therefore sets the Y-values to 41 data points as implied by: Worksheets(oSheet.Name).Range(Cells(3, Col_N), Cells(RowNo, Col_N)) Your code also sets the X-Values to 42 data points with the formula: "='17AE'!R3C13:R42C13" This doesn't cause me the error you describe but may for you. 4. Your code implies that your chart is an XY-Scatter. If not, it doesn't cause me the error you describe but may for you. Regards, Greg |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming Chart Series
Hi Greg,
I really appreciate the help. I have changed the code in a number of ways and each time when I try to set the xvalue I get the error. .XValues = oSheet.Range(Cells(3, Col_M), Cells(RowNo - 1, Col_M)) I have global constants for all the Col_(letter) variables. I checked for protection or locked cells and I can not find any. Ken "Greg Wilson" wrote: 1. Is the worksheet protected? I get the error you describe in this case. 2. Is the spelling of sheet "17AE" correct? Perhaps there is a space in the name not included in the formula? I get this error in this case. 3. Note that your code sets RowNo to 43 and therefore sets the Y-values to 41 data points as implied by: Worksheets(oSheet.Name).Range(Cells(3, Col_N), Cells(RowNo, Col_N)) Your code also sets the X-Values to 42 data points with the formula: "='17AE'!R3C13:R42C13" This doesn't cause me the error you describe but may for you. 4. Your code implies that your chart is an XY-Scatter. If not, it doesn't cause me the error you describe but may for you. Regards, Greg "Ken Kazinski" wrote: Hi Greg, Thanks for the help but I still get error 1004: Unable to set xvalues of the series. I copied the code but still I am getting errors. I am using excel 2003 SP1. Thanks, Ken "Greg Wilson" wrote: The following assessment was very hasty. The appended code appears to work. Your code didn't give Col_N a value (therefore, default = 0), and, although not essential, you didn't declare either RowNo or Col_N. Also, I believe you intended to use oChartObj instead of oChart in the following line: With oChart.Chart.SeriesCollection(1) I believe the above should be: With oChartObj.Chart.Series(1) The expression Worksheets(oSheet.Name) returns a worksheet object. However, you already defined the variable oSheet as refering to a worksheet. So, you need only use oSheet in place of Worksheets(oSheet.Name). Unqualified ranges default to the active sheet. In the following line, if oSheet isn't the active sheet then the Cells method needs to be qualified: Worksheets(oSheet.Name).Range(Cells(3, Col_N), Cells(RowNo, Col_N)) Using ws in place of oSheet, I believe the above should have been: ws.Range(ws.Cells(3, Col_N), ws.Cells(RowNo, Col_N)) I substituted ws for oSheet in the below code to conserve space and hopefully avoid workwrap. Dim ws As Worksheet Dim oChartObj As ChartObject Dim RowNo As Long, Col_N As Long Set ws = ThisWorkbook.Worksheets("Sheet1") RowNo = 43: Col_N = 3 'change value to suit Set oChartObj = ws.ChartObjects("Chart 1") With oChartObj .Top = ws.Rows(RowNo + 1).Top .Left = ws.Columns("E").Left .Width = ws.Columns("R").Left - ws.Columns("E").Left .Height = ws.Rows(RowNo + 29).Top - ws.Rows(RowNo + 1).Top With .Chart.SeriesCollection(1) .XValues = "='17AE'!R3C13:R42C13" .Values = ws.Range(ws.Cells(3, Col_N), ws.Cells(RowNo, Col_N)) End With End With End Sub Regards, Greg "Ken Kazinski" wrote: Can someone help with the code snipet below. I am trying to program a chart series but keep getting errors. Thanks, Ken Dim oSheet As Worksheet Dim oChartObj As ChartObject Dim Series As Series Set oSheet = ThisWorkbook.Worksheets(WorkSheetName) RowNo = 43 Set oChartObj = oSheet.ChartObjects("Chart 1") With oChartObj .Top = oSheet.Rows(RowNo + 1).Top .Left = oSheet.Columns("E").Left .Width = oSheet.Columns("R").Left - oSheet.Columns("E").Left .Height = oSheet.Rows(RowNo + 29).Top - oSheet.Rows(RowNo + 1).Top End With ' Location Reports With oChart.Chart.SeriesCollection(1) ' "Location Reports") .XValues = "='17AE'!R3C13:R42C13" .Values = Worksheets(oSheet.Name).Range(Cells(3, Col_N), Cells(RowNo, Col_N)) End With |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming Chart Series
1. Are there data in Sheet "17AE" range M3:M42 as defined by the formula:
"='17AE'!R3C13:R42C13" 2. Are there data in the X-Values range as defined by the code you listed ? 3. Is the worksheet actually spelled "17AE" (i.e. no spaces) ? What I think is likely happening is that once one of the series foumula lists an invalid reference then you are in a cul-de-sac and can't programmatically change it. If you first manually correct the formula(s) and then run the code it might work. If not successful feel free to email me a copy of the project and I can have a look. I suggest that you delete any unnecessary sheets & data and just send a simplified version that still exhibits the problem. Remove the text "SpammersDie!!!" from the following address: Regards, Greg "Ken Kazinski" wrote: Hi Greg, I really appreciate the help. I have changed the code in a number of ways and each time when I try to set the xvalue I get the error. .XValues = oSheet.Range(Cells(3, Col_M), Cells(RowNo - 1, Col_M)) I have global constants for all the Col_(letter) variables. I checked for protection or locked cells and I can not find any. Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel chart maximum series should more than 255 series | Charts and Charting in Excel | |||
User Selectable Series and Number of Series for Line Chart | Charts and Charting in Excel | |||
how to plot column chart with one series against multiple series. | Charts and Charting in Excel | |||
chart data series -- plot a table as a single series | Charts and Charting in Excel | |||
Series fill of "patchy" Data set or some Programming fix | Excel Worksheet Functions |