VBA : Convert Range in Chart to an Array

[penbeacho]

I am having issues figuring out how to add a chart with vba, I have coded several things and seem to be getting the right result now. The problem is I would like to use a vector/array to store the data that is to be plotted. I am not being successful with this, I can only get vba to plot with a range from a specific sheet. The successful script is :

ActiveChart.FullSeriesCollection(1).Name = "=Sheet1!$D$1:$D$6"
ActiveChart.FullSeriesCollection(1).Values = "=Sheet1!$E$1:$E$6"
ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$D$1:$D$6"

but what i want is something like this:

NameArray = Array(1,2,3......)
ValueArray = Array(1,2,3......)
XValueArray= Array(1,2,3......)

ActiveChart.FullSeriesCollection(1).Name = NameArray
ActiveChart.FullSeriesCollection(1).Values = ValueArray
ActiveChart.FullSeriesCollection(1).XValues = XValueArray

With this method I would be able to create a array repository for my data that is dynamic in code vs a static spreadsheet value.

Any help with this would be appreciated!

[TMS]

You would normally do that by creating Dynamic Named Ranges for each of the series and using those in the charts instead of the static ranges.

[Kenneth Hobson]

You can use Application.Transpose() or Application.Index().