vba 为具有相同 x 值但不同 y 值的多个图形创建 Excel 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20250070/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating an Excel macro for multiple graphs with the same x-values but different y-values
提问by blaaat
I'm trying to automate the creation of graphs in Excel by means of a macro. Basically I've got a lot of data (41 columns, starting from column C to column AQ) each one containing another 'name'. In one column there are two series in two different row sections (one from row 3 to 8, one from row 12 to 17).
我正在尝试通过宏在 Excel 中自动创建图形。基本上我有很多数据（41 列，从 C 列到 AQ 列），每个数据都包含另一个“名称”。在一列中，在两个不同的行部分中有两个系列（一个从第 3 行到第 8 行，一个从第 12 行到第 17 行）。
I want one graph per column, so in total I'll have 41 graphs. Each graph contains the two series. In column B are the values on the X axis, and this is for every graph the same.
我想要每列一张图，所以总共有 41 张图。每个图形包含两个系列。在 B 列中是 X 轴上的值，这对于每个图形都是相同的。
The purpose is to create a for next loop with the y-axis values, and have them all created at once.
目的是使用 y 轴值创建一个 for next 循环，并同时创建它们。
I've tried coding the first part (just creating one graph with on x-axis the values from B3:B8 and on y-axis the values from C3:C8, and the second series just a few rows down.
我已经尝试对第一部分进行编码（只是在 x 轴上创建一个图表，其中包含来自 B3:B8 的值，在 y 轴上创建来自 C3:C8 的值，第二个系列仅向下几行。
I get an error and I don't understand why, I've tried different approaches but it never works. The error situates in this line: ActiveChart.SeriesCollection(1).Values = Range(Cells(3, 3), Cells(8, 3)).Value
我收到一个错误，我不明白为什么，我尝试了不同的方法，但它从来没有奏效。错误位于这一行：ActiveChart.SeriesCollection(1).Values = Range(Cells(3, 3), Cells(8, 3)).Value
Does anyone have any ideas what may be wrong or how to tackle this problem?
有没有人有任何想法可能是错误的或如何解决这个问题？
Sub Macro5()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Blad1'!$A"
ActiveChart.SeriesCollection(1).XValues = "='Blad1'!$B:$B"
ActiveChart.SeriesCollection(1).Values = Range(Cells(3, 3), Cells(8, 3)).Value
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "='Blad1'!$A"
ActiveChart.SeriesCollection(2).XValues = "='Blad1'!$B:$B"
ActiveChart.SeriesCollection(2).Values = Range(Cells(12, 3), Cells(17, 3)).Value
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
ActiveChart.SeriesCollection(1).Trendlines.Add
ActiveChart.SeriesCollection(1).Trendlines(1).Select
Selection.DisplayEquation = True
Selection.DisplayRSquared = True
ActiveChart.SeriesCollection(2).Trendlines.Add
ActiveChart.SeriesCollection(2).Trendlines(1).Select
Selection.DisplayEquation = True
Selection.DisplayRSquared = True
End Sub
采纳答案by Jon Peltier
Range isn't qualified, and the array you convert it to using .Values is probably not understood by Excel.
范围未限定，Excel 可能无法理解您使用 .Values 将其转换为的数组。
Change
改变
ActiveChart.SeriesCollection(1).Values = Range(Cells(3, 3), Cells(8, 3)).Value
to
到
ActiveChart.SeriesCollection(1).Values = Worksheets("Blad1").Range(Cells(3, 3), Cells(8, 3))
or to
或者
ActiveChart.SeriesCollection(1).Values = "='Blad1'!$C:$C"
回答by bf2020
In excel 2007, SeriesCollection.Add()expects a range.
在 excel 2007 中，SeriesCollection.Add()期望范围。
