问题描述
我有一个包含多个Pareto图表的工作表(仪表板),另一个工作表(数据)通过标准$ A $ 1:$ B $ 2格式的公式为每个图表带来了范围.
I have a sheet (Dashboard) that has multiple Pareto charts, another sheet (Data) brings in the range for each chart via a formula in standard $A$1:$B$2 format.
如何使用表格数据"中的这些范围在仪表板"中的帕累托图中?图表名称在数据B4中图表范围在数据C4中我为每个图表提供了代码以进行故障排除,以下是单个图表中的代码
how do I use these ranges from the Sheet "Data" in the Pareto charts in the "Dashboard"?Chart name is in Data B4Chart Range is in Data C4I have code for each chart for troubleshooting below is one from a single chart
Sub FirstChart()
Dim FirstChartName As String
Dim FirstChartRange As Range
FirstChartName = Sheets("Data").Range("B4")
Set FirstChartRange = Worksheets("Data").Range(Sheets("Data").Range("C4").Value)
Sheets("Dashboard").ChartObjects("FirstChart").Activate
ActiveChart.ChartArea.Select
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = FirstChartName
ActiveChart.SetSourceData Source:=FirstChartRange
End Sub
谢谢.
更新:感谢@ coross24和@WIL.我已经根据他们对 https://gofile.io/d/8HfjQv
UPDATE:Thanks to @coross24 and @WIL.i have uploaded the file based on their answers to https://gofile.io/d/8HfjQv
推荐答案
Relik,
我不得不发布另一个答案,因为我的声誉不够高,无法发表评论.有一个绝对的肮脏的解决方法....似乎数据确实填充了图形,只是您绕过了错误消息,然后将y轴比例设置为auto.参见下面的代码:
Relik,
I've had to post another answer as my reputation isn't high enough to reply with a comment. There's an absolutely filthy work around.... it seems the data does actually populate the graph is you just bypass the error message, and then set the y-axis scale to auto. See below for the code:
Option Explicit
Sub FirstChart()
Dim FirstChartName As String
Dim FirstChartRange As String
Dim rng As Range
Dim r As Range
Dim shtData As Excel.Worksheet
Dim shtDashboard As Excel.Worksheet
Dim chart As Excel.chart
Dim tmp As Variant
Set shtData = ThisWorkbook.Sheets("Data")
Set shtDashboard = ThisWorkbook.Sheets("Dashboard")
' get chart name
FirstChartName = shtData.Range("B4").Value2
' get chart range
FirstChartRange = shtData.Range("C4").Value2
' change data for first chart
Set chart = shtDashboard.ChartObjects("FirstChart").chart
With chart
.HasTitle = True
.ChartTitle.Text = FirstChartName
On Error Resume Next
.SetSourceData shtData.Range(FirstChartRange)
On Error GoTo 0
.Axes(xlValue).MaximumScaleIsAuto = True
End With
End Sub
希望这有助于解决您的问题!
Hope this helps with your issue!
这篇关于Excel图表范围基于另一个工作表中单元格中的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!