Excel VBA：将循环遍历唯一名称组并创建相应图表的图表制作宏？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12685660/
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
Excel VBA: Chart-making macro that will loop through unique name groups and create corresponding charts?
提问by WATERflowTech
Alright, I've been racking my brain, reading up excel programming for dummies, and looking all over the place but I'm stressing over this little problem I have here. I'm completely new to vba programming, or really any programming language but I'm trying my best to get a handle on it.
好吧，我一直在绞尽脑汁，为傻瓜阅读 excel 编程，并四处寻找，但我正在为我在这里遇到的这个小问题感到压力。我对 vba 编程或任何编程语言完全陌生，但我正在尽我所能来处理它。
The Scenario and what my goal is:
场景和我的目标是：
The picture below is a sample of a huge long list of data I have from different stream stations. The sample only holds two (niobrara and snake) to illustrate my problem, but in reality I have a little over 80 stations worth of data, each varying in the amount of stress periods (COLUMN B).
下图是我从不同流站获得的一长串数据示例。样本仅包含两个（niobrara 和蛇）来说明我的问题，但实际上我有超过 80 个站点的数据，每个站点的压力期（B 列）数量各不相同。
COLUMN A, is the station name column. COLUMN B, stress period number COLUMN C, modeled rate COLUMN D, estimated rate
COLUMN A，是站名栏。B 列，压力期编号 C 列，建模速率 D 列，估计速率
What I have been TRYING to figure out is how to make a macro that will loop through the station names (COLUMN A) and for each UNIQUE Group of station names, make a chart that will pop out to the right of the group, say in the COLUMN E area.
我一直试图弄清楚的是如何制作一个宏来循环遍历电台名称（A 列），并且对于每个唯一的电台名称组，制作一个会在该组右侧弹出的图表，例如E 列区域。
The chart is completely simple, it just needs two series scatterplot/line chart; one series with COLUMN B as x-value and COLUMN C as y-value; and the other series needs COLUMN B as x-value and COLUMN D as y-value.
图表非常简单，只需要两个系列的散点图/折线图；以 COLUMN B 作为 x 值和 COLUMN C 作为 y 值的一系列；另一个系列需要 COLUMN B 作为 x 值，COLUMN D 作为 y 值。
Now my main ordeal, is that I don't know how to make the macro distinguish between station names, use all the data relating to that name to make the chart, then looping on to the next Station group and creating a chart that corresponds for that, and to continue looping through all 80+ station names in COLUMN A and to make the corresponding 80+ charts to the right of it all in somewhere like the COLUMN E.
现在我的主要磨难是我不知道如何让宏区分站名，使用与该名称相关的所有数据制作图表，然后循环到下一个站组并创建对应于并继续循环遍历 A 列中的所有 80 多个站名，并在其右侧的所有位置（例如 E 列）中制作相应的 80 多个图表。
If I had enough points to "bounty" this, I would in a heartbeat. But since I do not, whoever can solve my dilemma would receive my sincere gratitude in helping me understand run this problem smoothly and hopefully better my understanding of scenarios like this in the future. If there is anymore information that I need to clarify to make my question more understandable please comment your query and I'd be happy to explain in more detail the subject.
如果我有足够的积分来“赏金”这个，我会心跳加速。但既然我没有，谁能解决我的困境，我会衷心感谢帮助我理解这个问题的顺利运行，并希望我将来能更好地理解这样的场景。如果我需要澄清更多信息以使我的问题更易于理解，请评论您的查询，我很乐意更详细地解释该主题。
Cheers.
干杯。
Oh, and for extra credit; now that I think about it, I manually entered the numbers in COLUMN B. Since the loop would need to use that column as the x-value it would be important if it could loop through itself and fill that column on its own before it made the chart (I would imagine it would have something to do with anything as simple as "counting out the rows that correspond to the station name". But again, I know not the proper terminology to correspond the station name, hence the pickle I'm in; however if the veteran programmer who is savvy enough to answer this question could, I'd imagine such a piece of code would be simple enough yet crucial to the success of such a macro I seek.
哦，还有额外的功劳；现在我考虑了一下，我在 B 列中手动输入了数字。由于循环需要使用该列作为 x 值，所以如果它可以循环遍历自身并在它生成之前自行填充该列，这将很重要图表（我想它与“计算与车站名称相对应的行”这样简单的事情有关。但同样，我不知道对应车站名称的正确术语，因此泡菜我m in；但是，如果足够精明的资深程序员可以回答这个问题，我想这样一段代码将足够简单，但对我寻求的这样一个宏的成功至关重要。
回答by chris neilsen
Try this
尝试这个
Sub MakeCharts()
Dim sh As Worksheet
Dim rAllData As Range
Dim rChartData As Range
Dim cl As Range
Dim rwStart As Long, rwCnt As Long
Dim chrt As Chart
Set sh = ActiveSheet
With sh
' Get reference to all data
Set rAllData = .Range(.[A1], .[A1].End(xlDown)).Resize(, 4)
' Get reference to first cell in data range
rwStart = 1
Set cl = rAllData.Cells(rwStart, 1)
Do While cl <> ""
' cl points to first cell in a station data set
' Count rows in current data set
rwCnt = Application.WorksheetFunction. _
CountIfs(rAllData.Columns(1), cl.Value)
' Get reference to current data set range
Set rChartData = rAllData.Cells(rwStart, 1).Resize(rwCnt, 4)
With rChartData
' Auto fill sequence number
.Cells(1, 2) = 1
.Cells(2, 2) = 2
.Cells(1, 2).Resize(2, 1).AutoFill _
Destination:=.Columns(2), Type:=xlFillSeries
End With
' Create Chart next to data set
Set chrt = .Shapes.AddChart(xlXYScatterLines, _
rChartData.Width, .Range(.[A1], cl).Height).Chart
With chrt
.SetSourceData Source:=rChartData.Offset(0, 1).Resize(, 3)
' --> Set any chart properties here
' Add Title
.SetElement msoElementChartTitleCenteredOverlay
.ChartTitle.Caption = cl.Value
' Adjust plot size to allow for title
.PlotArea.Height = .PlotArea.Height - .ChartTitle.Height
.PlotArea.Top = .PlotArea.Top + .ChartTitle.Height
' Name series'
.SeriesCollection(1).Name = "=""Modeled"""
.SeriesCollection(2).Name = "=""Estimated"""
' turn off markers
.SeriesCollection(1).MarkerStyle = -4142
.SeriesCollection(2).MarkerStyle = -4142
End With
' Get next data set
rwStart = rwStart + rwCnt
Set cl = rAllData.Cells(rwStart, 1)
Loop
End With
End Sub
