Sub Ohh(chtname As String, chtsheet As String, chttitle As String, chtrangex As Range, chtrangey As Range, chtrangey1 As Range, chtrangey2 As Range, chtxaxistitle As String, chtyaxistitle As String)
Dim OlaCab1 As Series
Dim olacab As SeriesCollection
On Error GoTo ErrorHandling
With Charts(chtname)
.ChartType = xlLine
.HasTitle = True
.ChartTitle.Text = chttitle
Set OlaCab1 = olacab.NewSeries
ActiveChart.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = chtrangex
Activechart.seriescollection.formula= "=series(""jan"",chtrangex,chtrangey,1)"
'ActiveChart.SeriesCollection.NewSeries
'.SeriesCollection(3).XValues = chtrangex
'ActiveChart.SeriesCollection.NewSeries
'.SeriesCollection(3).XValues = chtrangex
Set olacab = ActiveChart.SeriesCollection
' For Each OlaCab1 In olacab
' OlaCab1.Name = Sheets("Sheet1").Cells(1, i + 2)
' i = i + 1
' Next OlaCab1
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = chtxaxistitle
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = chtyaxistitle
End With
Exit Sub
ErrorHandling:
MsgBox "No Chart found create one?"
Charts.Add
ActiveChart.Name = chtname
Resume
End Sub
Sub CallOhh()
Dim chtrangex As Range, chtrangey As Range, chtrangey1 As Range, chtrangey2 As Range
Set chtrangex = Sheets("Sheet1").Range(Sheets("Sheet1").Range("A2"), Sheets("Sheet1").Range("A2").End(xlDown))
Set chtrangey = Sheets("Sheet1").Range(Sheets("Sheet1").Range("B2"), Sheets("Sheet1").Range("B2").End(xlDown))
Set chtrangey1 = Sheets("Sheet1").Range(Sheets("Sheet1").Range("C2"), Sheets("Sheet1").Range("C2").End(xlDown))
Set chtrangey2 = Sheets("Sheet1").Range(Sheets("Sheet1").Range("D2"), Sheets("Sheet1").Range("D2").End(xlDown))
Call Ohh("Chart1", "Sheet1", "Fruits sales", chtrangex, chtrangey, chtrangey1, chtrangey2, "Fruits", "Sales")
End Sub
Here in this code i have declared set set ranges chtrangex and chtrangey for 1st serie(i have shown only ist serie here).and then i used activechart.seriescollection(1).formula = ........so many ways but the declared ranges seems to not working in the code above. It is even not working in the line seriescollection(1).xvalues = chtrangex. please help. But i know how to successfully create chart like using seriescollection.add property.My data is in sheet1.
|
jan |
feb |
mar |
apple |
208 |
215 |
239 |
bannana |
272 |
156 |
102 |
grapes |
124 |
82 |
112 |
orange |
214 |
69 |
139 |
cherry |
278 |
91 |
285 |
strawberry |
235 |
291 |
257 |
jackfruit |
221 |
287 |
84 |
peers |
292 |
175 |
90 |