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.

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

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



"(E-Mail Removed)" 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.
>

> 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



"(E-Mail Removed)" 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.
>

> 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
<BarbReinha...@discussions.microsoft.com> wrote:
> Do you have 4 "?
> --
> HTH,
> Barb Reinhardt
>
> "harm.char...@gmail.com" 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.
> >

> > 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.

<(E-Mail Removed)> wrote in message
news:9fedd499-05c6-4fb6-94bc-(E-Mail Removed)...
> On Aug 5, 4:58 pm, Barb Reinhardt
> <BarbReinha...@discussions.microsoft.com> wrote:
>> Do you have 4 "?
>> --
>> HTH,
>> Barb Reinhardt
>>
>> "harm.char...@gmail.com" 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.
>> >

>> > 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:
> <harm.char...@gmail.com> wrote in message
>
> news:9fedd499-05c6-4fb6-94bc-(E-Mail Removed)...
>
>
>
> > On Aug 5, 4:58 pm, Barb Reinhardt
> > <BarbReinha...@discussions.microsoft.com> wrote:
> >> Do you have 4 "?
> >> --
> >> HTH,
> >> Barb Reinhardt
>
> >> "harm.char...@gmail.com" 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.
> >> >

> >> > 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



<(E-Mail Removed)> 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
>
> <harm.char...@gmail.com> 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.