Code for the real brains out their..

[drag-driver]

The code below is used to create a dynamic macrow which adds a new series to a chart when the the date changes. It is not complete as i will try set it up for x and y once i get the problem at the moment sorted. The code as i see it defines date to be = to the last value entered in sheet 2 how ever when it come to the chart updating part using the defined entry it gives a complie error , any obnviuos mistakes or is the something technicaly wronge with what i am trying to do.


Sub tom_test()
Sheets("sheet1").Select
Range("A8").Select
ActiveWorkbook.Names.Add Name:="date", RefersToR1C1:= _
"=OFFSET(Sheet2!b1,MATCH(9.99999999999999E+307,Sheet2!b:b)-1,0,1,1)"
Sheets("sheet2").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = "=TOM.XLS!DATE" #this is where it has a problem#
ActiveChart.SeriesCollection(2).Values = "='sheet1'!R138C4:R138C26"
ActiveChart.SeriesCollection(2).Name = "='sheet1'!R138C3"
ActiveWindow.Visible = False
Windows("tom.xls").Activate
Sheets("sheet1").Select

End Sub

[Jackeb]

charts in excel are a pain in the ***.

Ihave resorted to recreating the chart each time, this has actually proved beneficial and really speeded u my program. I would suggest.

to delete just use the command:

activechart.seriescollection(2).delete

and then the new series bit!

Ed

only a suggestion mind


[ This Message was edited by: Jackeb on 2002-05-21 08:27 ]

[Ian Mac]

On 2002-05-21 08:07, drag-driver wrote:
The code below is used to create a dynamic macrow which adds a new series to a chart when the the date changes. It is not complete as i will try set it up for x and y once i get the problem at the moment sorted. The code as i see it defines date to be = to the last value entered in sheet 2 how ever when it come to the chart updating part using the defined entry it gives a complie error , any obnviuos mistakes or is the something technicaly wronge with what i am trying to do.


Sub tom_test()
Sheets("sheet1").Select
Range("A8").Select
ActiveWorkbook.Names.Add Name:="date", RefersToR1C1:= _
"=OFFSET(Sheet2!b1,MATCH(9.99999999999999E+307,Sheet2!b:b)-1,0,1,1)"
Sheets("sheet2").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = "=TOM.XLS!DATE" #this is where it has a problem#
ActiveChart.SeriesCollection(2).Values = "='sheet1'!R138C4:R138C26"
ActiveChart.SeriesCollection(2).Name = "='sheet1'!R138C3"
ActiveWindow.Visible = False
Windows("tom.xls").Activate
Sheets("sheet1").Select

End Sub

Sorry to mention it BUT you've started a lot of threads for the same question (Same as in Relates).

There is no need to this, it just confuses people when they have to jump backward and forward to try and catch any IMPORTANT infomation you may have missed in this post.

For the benefit of the others:

http://www.mrexcel.com/board/viewtop...8969&forum=2&3

http://www.mrexcel.com/board/viewtop...8949&forum=2&0

http://www.mrexcel.com/board/viewtop...8952&forum=2&0

http://www.mrexcel.com/board/viewtop...8984&forum=2&3

http://www.mrexcel.com/board/viewtop...8965&forum=2&1

As I said before, I left the door open for the REAL VBer's. I think you question there was of a better nature and contains more information into your problem