voyagerphoen
01-14-2019, 08:12 AM
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
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