Error 1004 using not contiguous data

  • Thread starter Thread starter Liughi
  • Start date Start date
L

Liughi

Preface: sorry for my english. :)

I faced with an annoying problem while chosing as "SeriesCollection" a not
contiguous range in the same row. I don't know if i'm using the right word,
the result of the recording of the desidered behaviour is:

ActiveChart.SeriesCollection(1).Values = "=(temp!R1C4,temp!R1C6,temp!R1C8)"

This is wonderful and works fine. Actually my macro works with dynamic data,
that's why i cannot use this fixed formula.

So, my Sub creates the desidered range using a series of such a cycle:

Dim rZona(5) As String
Dim rZ(5) As Range

If (here is mycondition) Then
If (rZona(i) = "") Then
rZona(i) = "(temp!" & ActiveCell.Offset(0, 1).Address
'this is the first one followed by some other cycle with
Offset(0,INSERT_OFFSET_HERE)
Else
rZona(i) = rZona(i) & ",temp!" & ActiveCell.Offset(0, 1).Address
End If
i = i + 1
End If

Followed, at end, by

If (here is mycondition) Then
For i = 1 To iMax
rZona(i) = rZona(i) & ")"
Set rZ(i) = Range(rZona(i))
Next i
End If

It seems to work fine, in fact if i add in this very last cycle the line:
rZ(i).Select
in my sheet the correct cells are highlighted!

But... when i try to write:

ActiveChart.SeriesCollection(i).Values = rZ(i)

Such as error is returned: error - unable to set the XValues property of the
series class.

That's quite awful.

Chart info:
ActiveChart.SetSourceData Source:=Sheets("temp").Range(E1:AG1),
PlotBy:=xlRows
ActiveChart.ChartType = xlColumnStacked

I hope sincerely that someone is able to help me, i spent the last two days
trying in anyway to solve this problem... but success ->0

Thank you in advance,
Luca.
 
Notice your first line
ActiveChart.SeriesCollection(1).Values =
"=(temp!R1C4,temp!R1C6,temp!R1C8)"
Click to expand...

is in R1C1 notation. Later you are using .Address here
rZona(i) = "(temp!" & ActiveCell.Offset(0, 1).Address
Click to expand...

and here
rZona(i) = rZona(i) & ",temp!" & ActiveCell.Offset(0,
1).Address
Click to expand...

Change .Address to .Address(ReferenceStyle:=xlR1C1), and see if it now
works.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top