Unable to get named range in Excel

Question

I have a worksheet where there is a chart on the first sheet, showing some data from a named range.
The named range looks like this:

=OFFSET(chart_data!$B$2,0,0,COUNTA(chart_data!$B:$B)-1)

where chart_data is a different sheet.
I also have a VBA script that is supposed to set colors of the chart same as background colors of corresponding cells. The script follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cht As ChartObject
Dim i As Integer
Dim vntValues As Variant
Dim s As String
Dim myseries As Series
Dim nRange As Range

    For Each cht In ActiveSheet.ChartObjects
        For Each myseries In cht.Chart.SeriesCollection

            If myseries.ChartType <> xlPie Then GoTo SkipNotPie
            s = Split(myseries.Formula, ",")(2)
            vntValues = myseries.Values                

            For i = 1 To UBound(vntValues)
                If Range(s).Cells(i).Interior.Color <> 16777215 Then
                    myseries.Points(i).Interior.Color = Range(s).Cells(i).Interior.Color
                End If
            Next i
SkipNotPie:
        Next myseries
    Next cht
End Sub

My problem is that when I try to evaluate Range(s), where s = "report!values_list", I get

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

How can I solve this?

@simoco, this produces an Application-defined or object-defined error — svz
1. Check the value of s by setting a breakpoint in interactive debug. 2. Check the name of named range and worksheet. — ttaaoossuuuu
@Taosique, already did that. seems to be correct. It works with normal ranges like report!$B$1:$B$5, but not with this one. — svz

Sharklord Sharklord · Accepted Answer · 2019-12-18T08:29:14

Try to do it on different Office. It works for me, some of Offices blocked the dynamic range. Try to make it on different PC with different type of MS Office and make them some simply task with dynamic range. If it will work, you just copy your code there and it will work even in your sheet.

Unable to get named range in Excel

2 Answers