Excel-ActiveChart.SetSourceData源:运行时错误"445":对象不支持此操作 [英] Excel - ActiveChart.SetSourceData Source: Runtime Error '445' : Object doesn't support this action

问题描述

我有一本excel工作簿(v2016)，在该工作簿中，我设法根据最大条目数来获取表的表名.此值(表的名称)在工作表"Pareto"单元格B4中.

I have an excel workbook (v2016), where I managed to get the table name of a table based on the highest number of entries. This value (which is a name of a table) is in Sheet "Pareto" Cell B4.

我正在使用以下代码使用上述表名来更新图表

I'm using the following code to update a chart using an above-mentioned table name

Sub GetTables()

Dim YTDL1TableName As String
Dim YTDL1Range As Range

YTDL1TableName = Sheets("Pareto").Range("B4")
Set YTDL1Range = Sheets("Pareto").Range(Sheets("Pareto").ListObjects(YTDL1TableName).Range.Address(True, True))
Sheets("Pareto").ChartObjects("YTDL1").Activate
ActiveChart.ChartArea.Select
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = YTDL1TableName
ActiveChart.SetSourceData Source:=YTDL1Range
End Sub

图表确实更新了，但我却得到了错误

While the charts do get updated I do get an error

运行时错误'445':对象不支持此操作

Runtime Error '445' : Object doesn't suuport this action

如何摆脱这个错误?

预先感谢

推荐答案

据我了解，您希望基于单元格中的值更改图表的数据源.

As I understand it, you want to change the data source of the chart based on the values in the cell.

请尝试以下代码:

Sub GetTables()

Dim YTDL1TableName As String
Dim YTDL1Range As Range


YTDL1TableName = Sheets("Pareto").Range("B4")
'Set YTDL1Range = Sheets("Pareto").Range(Sheets("Pareto").Cells(YTDL1TableName).Address(True, True))
Worksheets("Pareto").ChartObjects("YTDL1").Activate
ActiveChart.ChartArea.Select
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = YTDL1TableName
ActiveChart.SetSourceData Source:=Sheets("Pareto").Range("B4")

End Sub

结果如下:

希望有帮助！

这篇关于Excel-ActiveChart.SetSourceData源:运行时错误"445":对象不支持此操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！