"Add Series" Loop for Graphs

Posted by John on December 04, 2001 6:42 AM

General Question:
How do I make a loop for Adding Series Collections to a graph?

Example Loop I am using (doesn't work):
*******************************************************
For k = 0 To ((i / 2) - 1)

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(k + 1).Name = Mylist(k)
ActiveChart.SeriesCollection(k + 1).XValues = "=Sheet1R46C1:R796C1"
ActiveChart.SeriesCollection(k + 1).Values = "=Sheet1!R46C3:R796C3"

Next k
*****************************************************
Exact Question:
How do I add one (1) to the column number in the syntax "R46C3:R796C3" so that it reads "R46C4:R796C4?

Posted by Juan Jimenez on December 04, 2001 6:45 AM

Excellent question!!!!!!!!

Posted by Mike on December 04, 2001 7:09 AM

a couple of posts down below, 'Paul' advises us that within Protected sheets, using the TAB key will select the next UNLOCKED cell...a feature i was unaware of.

does anyone know how to change the TAB order? i think the default is to go by left to right across the first row and then down to the next row...basically it searches for UNLOCKED cells from A1-->ZZ1 and then down to B1... in VB, you can change TAB ORDER between buttons and listboxes...can you do that here somehow?

by the way, i think the 'PROTECTED sheet-UNLOCKED cell-TAB order' feature can be used as a simple trick so that if you have a massive array of data and need to constantly scroll up and down-left and right to certain high-traffic cells, you can set "pagemarks" or "hyperlinks" to these high-traffic cells within the excel worksheets to speed up worksheet movements.

han

Posted by Damon Ostrander on December 04, 2001 12:49 PM

Hi John,

The key do doing this is to recognize that the XValues and Values properties must be set to a Range object, not a string describing the range. So your in your example the code should be something like:

With Worksheets("Sheet1")
ActiveChart.SeriesCollection(k + 1).XValues = .Range(.Cells(46,1),.Cells(796,1))
ActiveChart.SeriesCollection(k + 1).Values = .Range(.Cells(46,k+2),.Cells(796,k+2))
End With

where I'm guessing k+2 is the column where you have the y values.

Happy charting.

Damon