需要使用vba宏循环在excel中创建一组图形 [英] Need to make a set of graphs in excel using a vba macro loop

问题描述

我试图制作一个宏，它将通过Excel工作表并遍历一组行，并为每一行生成一个图。

我有一些代码可以满足我需要，但是当我需要为每一行分别绘制一张图时，我会将它全部放在一张图上。

 `Dim i As Integer 
 Dim ws As Worksheet 
 Set ws = Sheets（Master Sheet ）
对于Row = 1到20 
 Dim my_cell 
 Dim rng As Range 
 Set rng = Sheets（Master Sheet）。Range（J8：Y8）
 
对于每个my_cell In rng 
如果my_cell<> 然后
 
 ActiveSheet.Shapes.AddChart.Select 
 ActiveChart.SetSourceData源：=范围（'Master Sheet'！$ J $ 8：$ Y $ 8）
 ActiveChart .ChartType = xlLineMarkers 
 ActiveChart.Location其中：= 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 
 
其他
退出对于找到空白单元格，退出
结束如果
下一个
 
下一行
结束小组
  

如果任何人都可以帮我一把，看看我哪里出了问题，那很好。

解决方案

不太确定您是否已经为 和 For Each 循环良好。理想情况下，您希望跨越范围并实际使用您在 For Each 语句中定义的值。没有看到你的工作簿，我只是调整了一小部分的数据来模拟创建一个图表。

我只是采用相同的代码，并在每行的同一工作表上生成图表数字。您可以采取这些原则并将其应用于您的逻辑。

 子测试（）
 Dim Row As Integer 
 Dim ws As Worksheet 
 Dim rng As Range 
 
 Set ws = Sheets（Sheet1）'Change this to：Set ws = Sheets（Master Sheet）
 
 Row = 1 to 6 
 Set rng = ws.Range（B1：D1）。Offset（Row，0）'Change to（I'm tryinging 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 '将其改为任何你想命名的图表。这当前被设置为通过在列A中设置的系列名称动态地命名每个图。
'ActiveChart.Location其中：= xlLocationAsNewSheet'取消注释此行以放置新工作表
'ws.Select'需要返回工作表
 
下一行
 
设置ws = nothing 
 Set rng = nothing 
 End Sub 
  

以下是一些可能有所帮助的链接;
在for循环VBA中创建和定位图和
Excel VBA：图表制作宏，将通过唯一名称组循环并创建相应的图表？

Google还有许多其他链接。 / p>

如果我误解了您的问题或需要其他东西，请告诉我。

干杯

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.

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$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` 

If anyone can give me a hand to see where I'm going wrong that would be great.

解决方案

Not really sure you have structured your For Next and For Each loops well. Ideally you'd like to step through ranges and actually use the value you define in your For Each statement. Without seeing your workbook I just adapted a small range of data to simulate creating a graph.

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$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

Here are a couple links that may help; Creating and positioning graphs in a for loop VBA and Excel VBA: Chart-making macro that will loop through unique name groups and create corresponding charts?

Google also has many other links.

If I've misunderstood your question or need anything else please let me know.

Cheers

这篇关于需要使用vba宏循环在excel中创建一组图形的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！