vba chart series formula property

Code:

---

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

I found a solution for this code i.e use address function with range variables i.e.
chtrangex.address(,,,true)......in the formula string.Any way if others have more thoughts please reply.

SetSourceData method

Code:

---

Option Explicit


Sub test()
    Dim cht As Chart
    Const chtname = "Chart1"
    Const chtsheet = "Sheet1"
    Const chttitle = "Fruits sales"
    Const chtxaxistitle = "Fruits"
    Const chtyaxistitle = "Sales"
    Dim chtrange As Range
   
    Set chtrange = Sheets(chtsheet).Cells(1).CurrentRegion
   
    On Error Resume Next
    Set cht = Charts(chtname)
    On Error GoTo 0
    If cht Is Nothing Then
        Set cht = Charts.Add
        cht.Name = chtname
    End If
   
    With cht
        .ChartType = xlLine
        .HasTitle = True
        .ChartTitle.Text = chttitle
       
        .SetSourceData chtrange
               
        .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
       
End Sub

---