Excel图表范围基于另一个工作表中单元格中的值 [英] Excel Chart range based on the value in a cell in another sheet

问题描述

我有一个包含多个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 B4 Chart Range is in Data C4 I 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图表范围基于另一个工作表中单元格中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！