问题描述
我有一个问题,在我的VBA代码中的几个图表的循环。我99.7%肯定这是一个很容易和快速的修复,但我的大脑是不工作今天。
我想代码循环通过ActiveSheet,对于图表包含的每个数据系列,我希望它添加系列的最后一个值。在我的例子中,我有9个图表,每个有3个系列在其中(绑定更改,有些有2但我离题)。
我有以下代码
Sub AddLastValue()
Dim myChartObject As ChartObject
Dim myChart As Chart
Dim mySrs As Series
Dim myPts As Points
使用ActiveSheet
对于每个myChartObject In .ChartObjects
对于每个myChart In .Chart
对于每个mySrs在.SeriesCollection
设置myPts = .Points
myPts(myPts.Count).ApplyDataLabels类型:= xlShowValue
下一个
下一个
下一个
结束于
$ b b End Sub
如果我删除循环代码,只是做一个
设置myPts = ActiveSheet.ChartObjects(1).Chart。 _
SeriesCollection(1).Points
myPts(myPts.Count).ApplyDataLabels type:= xlShowValue
b $ b
然后它适用于那个特定的图表和系列,所以我肯定是循环,我正在搞乱。
Sub AddLastValue()
Dim myChartObject As ChartObject
Dim mySrs As Series
Dim myPts As Points
使用ActiveSheet
对于每个myChartObject In .ChartObjects
对于每个mySrs在myChartObject.Chart.SeriesCollection
设置myPts = mySrs.Points
myPts(myPts.Count).ApplyDataLabels类型:= xlShowValue
Next
Next
End With
End Sub
I'm having an issue with the looping through of several charts in my VBA code. I'm 99.7% sure that this is a really easy and quick fix but my brain isn't working today.
I want the code to loop through every chart on the ActiveSheet, and for every data series that the chart contains I want it to add the last value of the series. In my example I have 9 charts, each with 3 series in them (bound to change, some have 2 but I digress).
I have the following code
Sub AddLastValue()
Dim myChartObject As ChartObject
Dim myChart As Chart
Dim mySrs As Series
Dim myPts As Points
With ActiveSheet
For Each myChartObject In .ChartObjects
For Each myChart In .Chart
For Each mySrs In .SeriesCollection
Set myPts = .Points
myPts(myPts.Count).ApplyDataLabels Type:=xlShowValue
Next
Next
Next
End With
End Sub
If I remove the looping code and just do a
Set myPts = ActiveSheet.ChartObjects(1).Chart. _
SeriesCollection(1).Points
myPts(myPts.Count).ApplyDataLabels type:=xlShowValue
Then it works for that specific chart and series, so I'm positive it is the looping that I'm messing up.
Could someone tell me where I mess up the looping code?
Try following code:
Sub AddLastValue()
Dim myChartObject As ChartObject
Dim mySrs As Series
Dim myPts As Points
With ActiveSheet
For Each myChartObject In .ChartObjects
For Each mySrs In myChartObject.Chart.SeriesCollection
Set myPts = mySrs.Points
myPts(myPts.Count).ApplyDataLabels Type:=xlShowValue
Next
Next
End With
End Sub
这篇关于VBA循环遍历所有图表中的所有系列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!