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

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

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

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

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

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

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