vba 需要使用vba宏循环在excel中制作一组图形
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12905087/
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
Need to make a set of graphs in excel using a vba macro loop
提问by user1748459
I'm trying to make a macro that will run through an excel sheet and go through a set of rows and make a graph for each row.
我正在尝试制作一个宏，该宏将通过 Excel 工作表运行并遍历一组行并为每一行制作一个图表。
I've got a bit of code that kind of does what I need, but puts it all on one graph when I need an individual graph for each row.
我有一些代码可以满足我的需要，但是当我需要每行一个单独的图形时，将它们全部放在一个图形上。
`Dim i As Integer
Dim ws As Worksheet
Set ws = Sheets("Master Sheet")
For Row = 1 To 20
Dim my_cell
Dim rng As Range
Set rng = Sheets("Master Sheet").Range("J8:Y8")
For Each my_cell In rng
If my_cell <> "" Then
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Master Sheet'!$J:$Y")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveSheet.Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "='Master Sheet'!$J:$Y"
ActiveChart.SeriesCollection(1).Name = "=""FP"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""Progress"""
ActiveChart.SeriesCollection(2).Values = _
"='Master Sheet'!$J,'Master Sheet'!$AF:$AH"
ActiveChart.DisplayBlanksAs = xlInterpolated
ActiveSheet.Activate
ActiveChart.ChartArea.Select
Else
Exit For ' Blank cell found, exiting
End If
Next
Next Row
End Sub`
If anyone can give me a hand to see where I'm going wrong that would be great.
如果有人可以帮我看看我哪里出错了，那就太好了。
回答by Rory
Not really sure you have structured your For Nextand For Eachloops well. Ideally you'd like to step through ranges and actually use the value you define in your For Eachstatement. Without seeing your workbook I just adapted a small range of data to simulate creating a graph.
不太确定您是否已经很好地构建了您的For Next和For Each循环。理想情况下，您希望遍历范围并实际使用您在For Each语句中定义的值。没有看到您的工作簿，我只是调整了一小部分数据来模拟创建图表。
I just took your same code and generate a graph on the same worksheet for each row of numbers. You can take these principles and apply it to your logic.
我只是采用了您的相同代码，并在同一工作表上为每一行数字生成了一个图表。您可以采用这些原则并将其应用于您的逻辑。
Sub test()
Dim Row As Integer
Dim ws As Worksheet
Dim rng As Range
Set ws = Sheets("Sheet1") 'Change this to: Set ws = Sheets("Master Sheet")
For Row = 1 To 6
Set rng = ws.Range("B1:D1").Offset(Row, 0) 'Change to (I'm guessing here): ws.Range("$J:$Y").Offset(Row, 0)
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(ws.Name & "!" & rng.Address)
ActiveChart.ChartType = xlLineMarkers
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$B:$D" 'Change to "='Master Sheet'!$J:$Y"
ActiveChart.SeriesCollection(1).Name = ws.Range("A1").Offset(Row, 0).Value 'Change this to whatever you want to name the graphs. This is currently set to dynamicly name each graph by the series name set in Column A.
'ActiveChart.Location Where:=xlLocationAsNewSheet 'uncomment this line to put on new sheet
'ws.Select 'Need to go back to worksheet
Next Row
Set ws = nothing
Set rng = nothing
End Sub
Here are a couple links that may help; Creating and positioning graphs in a for loop VBAand Excel VBA: Chart-making macro that will loop through unique name groups and create corresponding charts?
这里有几个可能有帮助的链接； 在 for 循环 VBA和 Excel VBA 中创建和定位图形：将循环遍历唯一名称组并创建相应图表的图表制作宏？
Google also has many other links.
谷歌还有许多其他链接。
If I've misunderstood your question or need anything else please let me know.
如果我误解了您的问题或需要其他任何信息，请告诉我。
Cheers
干杯
