HV_L
12-10-2009, 02:36 PM
Hi,
First post here, so please be gentle :think:
I have a chart which I want to create with VBA, because I'm using a template om which new (to be created sheets) are based.
I have this code now but I can't get the values properly assigned, who can help me out?? .. Thanks
Sub AddChart()
ActiveSheet.ChartObjects.Add(25, 1350, 301.5, 155.25).Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=ActiveSheet.Range("B85"), PlotBy:=xlColumns
ActiveWorkbook.Names.Add Name:="Jaar", RefersTo:= _
"=OFFSET(Basisgegevens!$G$2,COUNTA(Basisgegevens!$G$2:$G26)-1,0,-MIN(Basisgegevens!$K$2,COUNTA(Basisgegevens!$G$2:$G$22)-1),1)"
ActiveWorkbook.Names.Add Name:="Omzet", RefersTo:="Offset(Basisgegevens!Jaar, 0, 1)"
ActiveWorkbook.Names.Add Name:="Resultaat", RefersTo:="Offset(Basisgevens!Jaar, 0, 2)"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "=Basisgegevens!Omzet"
ActiveChart.SeriesCollection(1).Name = "=""Omzet"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Basisgegevens!Jaar"
ActiveChart.SeriesCollection(2).Values = "=Basisgegevens!Resultaat"
ActiveChart.SeriesCollection(2).Name = "=""Resultaat"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="2008"
End Sub
First post here, so please be gentle :think:
I have a chart which I want to create with VBA, because I'm using a template om which new (to be created sheets) are based.
I have this code now but I can't get the values properly assigned, who can help me out?? .. Thanks
Sub AddChart()
ActiveSheet.ChartObjects.Add(25, 1350, 301.5, 155.25).Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=ActiveSheet.Range("B85"), PlotBy:=xlColumns
ActiveWorkbook.Names.Add Name:="Jaar", RefersTo:= _
"=OFFSET(Basisgegevens!$G$2,COUNTA(Basisgegevens!$G$2:$G26)-1,0,-MIN(Basisgegevens!$K$2,COUNTA(Basisgegevens!$G$2:$G$22)-1),1)"
ActiveWorkbook.Names.Add Name:="Omzet", RefersTo:="Offset(Basisgegevens!Jaar, 0, 1)"
ActiveWorkbook.Names.Add Name:="Resultaat", RefersTo:="Offset(Basisgevens!Jaar, 0, 2)"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "=Basisgegevens!Omzet"
ActiveChart.SeriesCollection(1).Name = "=""Omzet"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Basisgegevens!Jaar"
ActiveChart.SeriesCollection(2).Values = "=Basisgegevens!Resultaat"
ActiveChart.SeriesCollection(2).Name = "=""Resultaat"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="2008"
End Sub