需要使用vba宏循环在excel中创建一组图形

Question

我正在尝试制作一个宏，它将通过Excel表运行并通过一组行并为每行制作一个图表。

我有一些代码可以满足我的需要，但是当我需要为每一行创建一个单独的图形时，我会将它们放在一个图形上。

`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$8:$Y$8")
 ActiveChart.ChartType = xlLineMarkers
 ActiveChart.Location Where:=xlLocationAsNewSheet
 ActiveSheet.Activate
 ActiveChart.PlotArea.Select
 ActiveChart.SeriesCollection(1).XValues = "='Master Sheet'!$J$2:$Y$2"
 ActiveChart.SeriesCollection(1).Name = "=""FP"""
 ActiveChart.SeriesCollection.NewSeries
 ActiveChart.SeriesCollection(2).Name = "=""Progress"""
 ActiveChart.SeriesCollection(2).Values = _
 "='Master Sheet'!$J$8,'Master Sheet'!$AF$8:$AH$8"
 ActiveChart.DisplayBlanksAs = xlInterpolated
 ActiveSheet.Activate
 ActiveChart.ChartArea.Select

 Else
 Exit For ' Blank cell found, exiting
 End If
 Next

 Next Row
 End Sub` 

如果有人能帮我看看我错在哪里，那就太好了。

Answer 1

不太确定是否已为下一个循环和每个循环构建了。理想情况下，您希望逐步遍历范围，并实际使用您在<代码>中为每个<代码>语句定义的值。在没有看到您的工作簿的情况下，我只是调整了一小部分数据来模拟创建图形。

我只需要使用相同的代码，在同一工作表上为每一行数字生成一个图形。您可以将这些原则应用到您的逻辑中。

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$7:$Y$7").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$1:$D$1" 'Change to "='Master Sheet'!$J$2:$Y$2"
 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

这里有几个链接可能会有所帮助；在for循环VBA和Excel VBA中创建和定位图形：图表制作宏将循环遍历唯一名称组并创建相应的图表？

谷歌还有许多其他链接。

如果我误解了你的问题，或者需要其他什么，请告诉我。

干杯干杯