Mismatch on trying to update chart series formula

Question

I can't figure out why I am getting a Mismatch when I am trying to update the Pie chart. Below is a test snippet. Particularly the issue is with the last statment, trying to update the series formula for the chart. I've tried a few methods but every instance where I use a range to update the chart it throws me a mismatch.

Private Sub UpdateAnalytics()

Dim CategoryPreview As Range
Dim ChartValues As Variant
Dim ChartXValues As Variant

    With Sheets("Analytics")
    Set CategoryPreview = Range(.Range("A1"), .Range("A1").End(xlDown)).Find("Page 4", LookIn:=xlValues)
    If 1 <> 1 Then
        CategoryPreview.Resize(1, 3).Insert Shift:=xlDown
        CategoryPreview.Resize(1, 3).Copy Destination:=CategoryPreview.Offset(-1, 0)
        CategoryPreview.Offset(-1, 0) = 1
        CategoryPreview.Offset(-1, 1) = 1
    ElseIf 1 = 1 Then
        CategoryPreview.Offset(1, 0).Resize(1, 3).Insert Shift:=xlDown
        CategoryPreview.Resize(1, 3).Copy Destination:=CategoryPreview.Offset(1, 0)
        CategoryPreview.Offset(1, 0) = 2
        CategoryPreview.Offset(1, 1) = 2
    End If

    .Range("AnnualSpent") = "=SUM(" & Range(.Range("B1"), .Range("B1").End(xlDown)).Address & ")"

    'Add new tab to the pie chart
    ChartXValues = Range(.Range("A1"), .Range("A1").End(xlDown))
    ChartValues = Range(.Range("B1"), .Range("B1").End(xlDown))

    .ChartObjects("AnalyticsChart").Activate


       ActiveChart.FullSeriesCollection(1).Select
       Selection.Formula = "=SERIES(,Analytics!" & ChartXValues & ",Analytics!" & ChartValues & ",1)"    <<<<< Mismatch happens here
    End With

End Sub

Answer 1

I ended up getting it with this

Private Sub UpdateAnalytics()
    Dim CategoryPreview As Range
    Dim ChartValues As String 'Stored as String instead of variant
    Dim ChartXValues As String 'Stored as String instead of variant
    Dim Cht As Chart
    Dim Srs As Series
        With Sheets("Analytics")
            Set CategoryPreview = .Range(.Range("A1"), .Range("A1").End(xlDown)).Find("Page 4", LookIn:=xlValues) '<~~ add period .Range(. range("a1'
            If 1 <> 1 Then
                CategoryPreview.Resize(1, 3).Insert Shift:=xlDown
                CategoryPreview.Resize(1, 3).Copy Destination:=CategoryPreview.Offset(-1, 0)
                CategoryPreview.Offset(-1, 0) = 1
                CategoryPreview.Offset(-1, 1) = 1
            ElseIf 1 = 1 Then
                CategoryPreview.Offset(1, 0).Resize(1, 3).Insert Shift:=xlDown
                CategoryPreview.Resize(1, 3).Copy Destination:=CategoryPreview.Offset(1, 0)
                CategoryPreview.Offset(1, 0) = 2
                CategoryPreview.Offset(1, 1) = 2
            End If

            .Range("AnnualSpent") = "=SUM(" & Range(.Range("B1"), .Range("B1").End(xlDown)).Address & ")"

            'Add new tab to the pie chart
            ChartXValues = .Range(.Range("A1"), .Range("A1").End(xlDown)).Address 'add period, captured address for a dynamic chart
            ChartValues = .Range(.Range("B1"), .Range("B1").End(xlDown)).Address 'add period, captured address for a dynamic chart

            Set Cht = .ChartObjects("AnalyticsChart").Chart
            Set Srs = Cht.SeriesCollection(1)
            Srs.Formula = "=SERIES(,Analytics!" & ChartXValues & ",Analytics!" & ChartValues & ",1)" 'Updated chart via the formula
        End With
End Sub

Though, what I don't quite understand is that I had to keep the Cht and Srs variables or it wouldn't work. Why would storing the chart and series be any different from using the following?

.ChartObjects("AnalyticsChart").SeriesCollection(1).Formula = "=SERIES(,Analytics!" & ChartXValues & ",Analytics!" & ChartValues & ",1)"