我有一个工作表,第一张纸上有一个图表,显示来自命名范围的一些数据 .
命名范围如下所示:
= OFFSET(chart_data!$ B $ 2,0,0,COUNTA(chart_data!$ B:$ B)-1)
chart_data是另一张表 .
我还有一个VBA脚本,应该将图表的颜色设置为与相应单元格的背景颜色相同 . 该脚本如下:
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
我的问题是,当我尝试评估 Range(s)
,其中s = "report!values_list"时,我得到了
运行时错误'1004':对象'_Worksheet'的方法'Range'失败
我怎么解决这个问题?
1 回答
您可以获得与此名称相对应的范围: