Excel Charting problem..

[Guest]

Hello

I am creating a chart and want to be able to change the yvalues depending
upon criteria selected in a dropdown box.

I have a problem with non-contiguous cells.

In my worksheets I want the yvals to be 6 values based upon non-conitguous
data e.g.

Cells(1,5) Cells(3, 5) Cells(5,7), Cells(7,7) Cells (9,7), Cells(11,7)

I need to use the Cells( row, column) format as opposed to Range("E1") for
example.

When using non-contiguous cells this will work...

Set yvals = Worksheets("Sheet 1").Range(Cells(1,5), Cells(5,5))
ActiveChart.SeriesCollection(1).Values = yvals


How do i do a similar thing but allow a refernce to non-coniguous cells as
described.?

I'd be both grateful and interested in learning how this can be done. Any
help would be warmly appreciated.

By the way, I am using Excel 97...

Regards


Alex

 

[Bryan Hessey]

I would probably go the simple route and select a range of 6 helper
y-cells and set them with the contents of the required non-contiguous
cells,
thus (using the Y column as a blank-helper)
Y1 = A1
Y2 =C5
Y3 =G7
Y4 =I7
Y5 =K7
etc, giving a contiguous set for use in your macro.