|
本帖最后由 jzzhmm425 于 2013-4-29 11:06 编辑
下面这段代码是给工作表中插入图表的一段代码,但是我加上循环后就无法运行了?谁能帮我加上循环,让每张工作表都能运行这段代码。
- Sub ChartAdd()
- Dim myRange As Range
- Dim myChart As ChartObject
- Dim R As Integer
- With Sheet1
- .ChartObjects.Delete
- R = .Range("A65536").End(xlUp).Row
- Set myRange = .Range("A" & 1 & ":B" & R)
- Set myChart = .ChartObjects.Add(120, 40, 400, 250)
- With myChart.Chart
- .ChartType = xlColumnClustered
- .SetSourceData Source:=myRange, PlotBy:=xlColumns
- .ApplyDataLabels ShowValue:=True
- .HasTitle = True
- .ChartTitle.Text = "图表制作示例"
- With .ChartTitle.Font
- .Size = 20
- .ColorIndex = 3
- .Name = "华文新魏"
- End With
- With .ChartArea.Interior
- .ColorIndex = 8
- .PatternColorIndex = 1
- .Pattern = xlSolid
- End With
- With .PlotArea.Interior
- .ColorIndex = 35
- .PatternColorIndex = 1
- .Pattern = xlSolid
- End With
- .SeriesCollection(1).DataLabels.Delete
- With .SeriesCollection(2).DataLabels.Font
- .Size = 10
- .ColorIndex = 5
- End With
- End With
- End With
- Set myRange = Nothing
- Set myChart = Nothing
- End Sub
复制代码 我把这段代码中的
With Sheet1
换成For x = 2 To Sheets.Count
With Sheets(x)
变成下面这段代码后,总提示说“ApplyDataLabels用作chart对象失败”,不知道是哪里错了。- Sub ChartAdd()
- Dim myRange As Range
- Dim myChart As ChartObject
- Dim R As Integer
- For x = 3 To Sheets.Count
- With Sheets(x)
- .ChartObjects.Delete
- R = .Range("A65536").End(xlUp).Row
- Set myRange = .Range("A" & 1 & ":B" & R)
- Set myChart = .ChartObjects.Add(120, 40, 400, 250)
- With myChart.Chart
- .ChartType = xlColumnClustered
- .SetSourceData Source:=myRange, PlotBy:=xlColumns
- .ApplyDataLabels ShowValue:=True
- .HasTitle = True
- .ChartTitle.Text = "图表制作示例"
- With .ChartTitle.Font
- .Size = 20
- .ColorIndex = 3
- .Name = "华文新魏"
- End With
- With .ChartArea.Interior
- .ColorIndex = 8
- .PatternColorIndex = 1
- .Pattern = xlSolid
- End With
- With .PlotArea.Interior
- .ColorIndex = 35
- .PatternColorIndex = 1
- .Pattern = xlSolid
- End With
- .SeriesCollection(1).DataLabels.Delete
- With .SeriesCollection(2).DataLabels.Font
- .Size = 10
- .ColorIndex = 5
- End With
- End With
- End With
- Set myRange = Nothing
- Set myChart = Nothing
- Next
- End Sub
复制代码
- Sub ChartAdd()
- Dim myRange As Range
- Dim myChart As ChartObject
- Dim R As Integer
- On Error Resume Next
- For x = 1 To Sheets.Count
- With Sheets(x)
- Debug.Print .Name, .ChartObjects.Count
-
- .ChartObjects.Delete
- R = .Range("A65536").End(xlUp).Row
- Set myRange = .Range("A" & 1 & ":B" & R)
- Set myChart = .ChartObjects.Add(120, 40, 400, 250)
- With myChart.Chart
- .ChartType = xlColumnClustered
- .SetSourceData Source:=myRange, PlotBy:=xlColumns
- .ApplyDataLabels ShowValue:=True
- .HasTitle = True
- .ChartTitle.Text = "图表制作示例"
- With .ChartTitle.Font
- .Size = 20
- .ColorIndex = 3
- .Name = "华文新魏"
- End With
- With .ChartArea.Interior
- .ColorIndex = 8
- .PatternColorIndex = 1
- .Pattern = xlSolid
- End With
- With .PlotArea.Interior
- .ColorIndex = 35
- .PatternColorIndex = 1
- .Pattern = xlSolid
- End With
- .SeriesCollection(1).DataLabels.Delete
- With .SeriesCollection(2).DataLabels.Font
- .Size = 10
- .ColorIndex = 5
- End With
- End With
- End With
- Set myRange = Nothing
- Set myChart = Nothing
- Next
- End Sub
复制代码
|
-
这是第二段代码运行后提示的错误
-
这是第一段代码运行后出来的表格
-
-
示例工作表.zip
7.66 KB, 下载次数: 3
|