424 error

[Boogerbut74]

The error im getting 424 error for sFmla = Series.Formula

Sub Test_7()


Dim lastrow As Integer
lastrow = Cells(Rows.Count, 10).End(xlUp).Row

'setting x and y axis
Set yData = ActiveSheet.Range("F11:t" & lastrow)
Set xData = ActiveSheet.Range("D11:D" & lastrow)
Set GraphRange = Union(xData, yData)

'Create a chart
   Set cht = ActiveSheet.ChartObjects.Add( _
    Left:=ActiveCell.Left, _
    Width:=450, _
    Top:=ActiveCell.Top, _
    Height:=250)

'Determine the chart type
cht.Chart.ChartType = xlXYScatterSmooth


 'edits chart detailes
cht.Chart.Axes(xlCategory).MinimumScale = 0
cht.Chart.Axes(xlCategory).MaximumScale = 100

cht.Chart.Axes(xlValue).MinimumScale = 115
cht.Chart.Axes(xlValue).MaximumScale = 140

cht.Chart.Legend.Position = xlLegendPositionBottom

'Set series name
 Dim sFmla As String
  Dim vFmla As Variant
  Dim sYVals As String
  Dim rYVals As Range
  Dim rName As Range
  
[B]  sFmla = Series.Formula[/B]
    ' e.g. =SERIES("Name",Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1)
  sFmla = Replace(sFmla, "=SERIES(", "")
  sFmla = Left$(sFmla, Len(sFmla) - 1)
    ' e.g. "Name",Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1
  vFmla = Split(sFmla, ",")
  
  If UBound(vFmla) + 1 - LBound(vFmla) = 4 Then
    sYVals = vFmla(LBound(vFmla) + 2)
      ' third element (first + 2)
      ' e.g. Sheet1!$B$2:$B$10
    On Error Resume Next
    Set rYVals = Range(sYVals)
    On Error GoTo 0
    If Not rYVals Is Nothing Then
      If rYVals.Cells.Count > 1 Then
        On Error Resume Next
        If rYVals.Columns.Count > rYVals.Rows.Count Then
          ' by row, take cell to left
          Set rName = rYVals.Resize(1, 1).Offset(, -1)
        Else
          ' by col, take cell above
          Set rName = rYVals.Resize(1, 1).Offset(-1)
        End If
        On Error GoTo 0
        If Not rName Is Nothing Then
          Series.Name = "=" & rName.Address(, , , True)
            ' e.g. "=Sheet1!$B$1"
            ' use formula notation so it links to cell
        End If
      End If
    End If
  End If
End Sub

 

[rlv01]

You are getting the 424 error because 'Series' is not an object.

Object required (Error 424)

Office VBA reference topic

learn.microsoft.com

You can't just use "Series" in that way. Like any object variable, you have to instance it with a SET statement.

    Dim chtSeries As series
    Dim n As Integer
    
    n = 1     'or whatever valid series index you want to use
    Set chtSeries = cht.Chart.SeriesCollection(n)
    chtSeries.Formula = "=SERIES(Sheet2!$D$10,,Sheet2!$D$11:$D$30,1)"

You could avoid so many of these kinds of errors if you declare all your variables. It will also make the VBE Intellisense feature work more consistently. If you add this statement:

Option Explicit

as the first line in your code module, it will require variable declaration for all variable used in code.