本文介绍了Excel图表范围基于另一个工作表中单元格中的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个包含多个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图表范围基于另一个工作表中单元格中的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-03 23:09
查看更多