Hi,


I recorded a copy of a chart and paste to new location on a work sheet
and recorded the value for the chart. I'm trying to use vba to do
this, but the following code has an error. What is wrong with it.
Error Unable to set XLValues of Series class.

What does this do?

debug.print fcol
debug.print lcol
debug.print "=reject!R2C" & Fcol + 1 & ":R3C" & Lcol - 2 & """"

--
HTH,
Barb Reinhardt



" wrote:

Hi,


I recorded a copy of a chart and paste to new location on a work sheet
and recorded the value for the chart. I'm trying to use vba to do
this, but the following code has an error. What is wrong with it.

Code:

 ActiveChart.SeriesCollection(1).XValues = "=reject!R2C" & Fcol + 1 &
 ":R3C" & Lcol - 2 & """"

Error Unable to set XLValues of Series class.

Do you have 4 "?
--
HTH,
Barb Reinhardt



" wrote:

Hi,


I recorded a copy of a chart and paste to new location on a work sheet
and recorded the value for the chart. I'm trying to use vba to do
this, but the following code has an error. What is wrong with it.

Code:

 ActiveChart.SeriesCollection(1).XValues = "=reject!R2C" & Fcol + 1 &
 ":R3C" & Lcol - 2 & """"

Error Unable to set XLValues of Series class.

On Aug 5, 4:58 pm, Barb Reinhardt
wrote:
Do you have 4 "?
--
HTH,
Barb Reinhardt

" wrote:
Hi,

I recorded a copy of a chart and paste to new location on a work sheet
and recorded the value for the chart. I'm trying to use vba to do
this, but the following code has an error. What is wrong with it.

Code:

  ActiveChart.SeriesCollection(1).XValues = "=reject!R2C" & Fcol + 1 &
  ":R3C" & Lcol - 2 & """"
 

Error Unable to set XLValues of Series class.

HI,

This is the recorded values with the change.
ActiveChart.SeriesCollection(1).XValues = "=reject!R2C37:R2C49"
This is the vba code I thought would work.
ActiveChart.SeriesCollection(1).XValues = "=reject!R2C" & Fcol + 1 &
":R3C" & Lcol - 2 & """

The Fcol and Lcol are correct and when cursor over them show the
correct value.

Debug show
=reject!R2C20:R3C32"

But still get error as above.

wrote in message
...
On Aug 5, 4:58 pm, Barb Reinhardt
wrote:
Do you have 4 "?
--
HTH,
Barb Reinhardt

" wrote:
Hi,

I recorded a copy of a chart and paste to new location on a work sheet
and recorded the value for the chart. I'm trying to use vba to do
this, but the following code has an error. What is wrong with it.

Code:

  ActiveChart.SeriesCollection(1).XValues = "=reject!R2C" & Fcol + 1 &
  ":R3C" & Lcol - 2 & """"
 

Error Unable to set XLValues of Series class.

HI,

This is the recorded values with the change.
ActiveChart.SeriesCollection(1).XValues = "=reject!R2C37:R2C49"
This is the vba code I thought would work.
ActiveChart.SeriesCollection(1).XValues = "=reject!R2C" & Fcol + 1 &
":R3C" & Lcol - 2 & """

The Fcol and Lcol are correct and when cursor over them show the
correct value.

Debug show
=reject!R2C20:R3C32"

But still get error as above.


That looks like a 2(row) x 13(col) range, but it should be a single row or
column. Looks like either "reject!R2C" or ":R3C" is wrong, I guess the
latter should be ":R2C"

Regards,
Peter T

On Aug 6, 8:34 am, "Peter T" <peter_t@discussions wrote:
wrote in message

...



On Aug 5, 4:58 pm, Barb Reinhardt
wrote:
Do you have 4 "?
--
HTH,
Barb Reinhardt

" wrote:
Hi,

I recorded a copy of achartand paste to new location on a work sheet
and recorded the value for thechart. I'm trying to use vba to do
this, but the following code has an error. What is wrong with it.

Code:

   ActiveChart.SeriesCollection(1).XValues = "=reject!R2C" & Fcol + 1 &
   ":R3C" & Lcol - 2 & """"
  

Error Unable to set XLValues of Series class.

HI,

This is the recorded values with the change.
ActiveChart.SeriesCollection(1).XValues = "=reject!R2C37:R2C49"
This is the vba code I thought would work.
ActiveChart.SeriesCollection(1).XValues = "=reject!R2C" & Fcol + 1 &
":R3C" & Lcol - 2 & """

The Fcol and Lcol are correct and when cursor over them show the
correct value.

Debug show
=reject!R2C20:R3C32"

But still get error as above.

That looks like a 2(row) x 13(col) range, but it should be a single row or
column. Looks like either "reject!R2C" or ":R3C" is wrong, I guess the
latter should be ":R2C"

Regards,
Peter T

You are correct, however after correcting it I still receive the
error.
"Unable to set name property of the series class".
I do not know if the way I have the formula formatted is my problem
What I do is copy the current chart to the right (2 columns) and then
change the property of the series class for the chart that was copied.
I recorded the change of the series class and was trying to the code
as stated above using the "Fcol" for the new location where the data
for the chart starts and "Lcol" for wher the data ends. I hope this
will be a little clearer.

That's a different problem. To change the series' name simply write to its
Name property.

If I understand the objective you want to offset all series data by the same
amount. Providing you are sure in advance the same offset will work in all
series (X & Y values & name, possibly bubble sizes) have a go with the
following.

Sub test()
Dim cht As Chart
Set cht = ActiveChart
If cht Is Nothing Then
MsgBox "select a chart"
Exit Sub
End If

OffsetChart ActiveChart, 0, 2

End Sub

Sub OffsetChart(cht As Chart, rowOS As Long, colOS As Long)
Dim i As Long
Dim posL As Long, posR As Long
Dim sFmla As String, sFmlaEnd As String
Dim r As Range
Dim sr As Series
Dim arr

ReDim arrFmlas(1 To cht.SeriesCollection.Count) As String

'store the original formulas for undo just in case
For i = 1 To cht.SeriesCollection.Count
arrFmlas(i) = cht.SeriesCollection(i).Formula
Next

For Each sr In cht.SeriesCollection

sFmla = sr.Formula
posL = InStr(1, sFmla, "(") + 1
posR = InStrRev(sFmla, ")") - 1
arr = Split(Mid$(sFmla, posL, (posR - posL)), ",")
sFmla = Left$(sFmla, posL - 1)

On Error Resume Next
For i = 0 To UBound(arr)
Set r = Nothing
Set r = Range(arr(i))

If Not r Is Nothing Then
arr(i) = r.Offset(rowOS, colOS).Address(external:=True)
End If
sFmla = sFmla & arr(i)
If i < UBound(arr) Then
sFmla = sFmla & ","
Else
sFmla = sFmla & ")"
End If
Next

On Error GoTo errH
sr.Formula = sFmla
Next

Exit Sub

resUndo:

If MsgBox("an error occurred, Undo ?", vbYesNo) = vbYes Then
On Error Resume Next
For i = 1 To UBound(arrFmlas)
cht.SeriesCollection(i).Formula = arrFmlas(i)
Next
End If
Exit Sub

errH:
Resume resUndo

End Sub


Regards,
Peter T



wrote in message
<snip
You are correct, however after correcting it I still receive the
error.
"Unable to set name property of the series class".
I do not know if the way I have the formula formatted is my problem
What I do is copy the current chart to the right (2 columns) and then
change the property of the series class for the chart that was copied.
I recorded the change of the series class and was trying to the code
as stated above using the "Fcol" for the new location where the data
for the chart starts and "Lcol" for wher the data ends. I hope this
will be a little clearer.

On Aug 6, 5:11 pm, "Peter T" <peter_t@discussions wrote:
That's a different problem. To change the series' name simply write to its
Name property.

If I understand the objective you want to offset all series data by the same
amount. Providing you are sure in advance the same offset will work in all
series (X & Y values & name, possibly bubble sizes) have a go with the
following.

Sub test()
Dim cht As Chart
Set cht = ActiveChart
If cht Is Nothing Then
MsgBox "select a chart"
Exit Sub
End If

OffsetChart ActiveChart, 0, 2

End Sub

Sub OffsetChart(cht As Chart, rowOS As Long, colOS As Long)
Dim i As Long
Dim posL As Long, posR As Long
Dim sFmla As String, sFmlaEnd As String
Dim r As Range
Dim sr As Series
Dim arr

ReDim arrFmlas(1 To cht.SeriesCollection.Count) As String

'store the original formulas for undo just in case
For i = 1 To cht.SeriesCollection.Count
arrFmlas(i) = cht.SeriesCollection(i).Formula
Next

For Each sr In cht.SeriesCollection

sFmla = sr.Formula
posL = InStr(1, sFmla, "(") + 1
posR = InStrRev(sFmla, ")") - 1
arr = Split(Mid$(sFmla, posL, (posR - posL)), ",")
sFmla = Left$(sFmla, posL - 1)

On Error Resume Next
For i = 0 To UBound(arr)
Set r = Nothing
Set r = Range(arr(i))

If Not r Is Nothing Then
arr(i) = r.Offset(rowOS, colOS).Address(external:=True)
End If
sFmla = sFmla & arr(i)
If i < UBound(arr) Then
sFmla = sFmla & ","
Else
sFmla = sFmla & ")"
End If
Next

On Error GoTo errH
sr.Formula = sFmla
Next

Exit Sub

resUndo:

If MsgBox("an error occurred, Undo ?", vbYesNo) = vbYes Then
On Error Resume Next
For i = 1 To UBound(arrFmlas)
cht.SeriesCollection(i).Formula = arrFmlas(i)
Next
End If
Exit Sub

errH:
Resume resUndo

End Sub

Regards,
Peter T

wrote in message

<snip

You are correct, however after correcting it I still receive the
error.
"Unable to set name property of the series class".
I do not know if the way I have the formula formatted is my problem
What I do is copy the current chart to the right (2 columns) and then
change the property of the series class for the chart that was copied.
I recorded the change of the series class and was trying to the code
as stated above using the "Fcol" for the new location where the data
for the chart starts and "Lcol" for wher the data ends. I hope this
will be a little clearer.

Peter T

Thanks for the code and help. Your code will help in the future.
However I did find out why I was getting the error.
It was the way I was formatting.

This is what I tried, but it produced the error.
..SeriesCollection(1).XValues = "=reject!R2C" & Fcol + 1 & ":R2C" &
Lcol - 2 & """"

This is the way the code should be.
..SeriesCollection(1).XValues = "=reject!R2C" & Fcol + 1 & ":R2C" &
Lcol - 2

Note the " " " Ihad them placed wrong.

Thanks to all that helped.