vba 优秀。在不同的工作表中创建图表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10199509/
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
Excel. Creating charts in different sheets
提问by jpcgandre
I need to create one chart per sheet in Excel. Here is my code:
我需要在 Excel 中为每张纸创建一个图表。这是我的代码：
Sheets(i).Activate
For Each cht In ActiveSheet.ChartObjects
cht.Delete
Next
'create chart
Set chtChart = ActiveSheet.ChartObjects.Add(Left:=75, Width:=300, Top:=75, Height:=300).Chart
With chtChart
.ChartType = xlXYScatterSmooth
Do While .SeriesCollection.Count <> 0
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
Loop
Set srsNew = .SeriesCollection.NewSeries
With srsNew
.XValues = "='" & Sheets(i).Name & "'!" & _
Sheets(i).Range(Range("K2"), Range("k2").End(xlDown)).Address
.Values = "='" & Sheets(i).Name & "'!" & _
Sheets(i).Range(Range("l2"), Range("l2").End(xlDown)).Address
End With
End With
For the first sheet this works but for the second, third ... it does not. It issues the error 1004 "application defined or object defined error" in XValues or Values. I also noticed that if I introduce
对于第一张纸，这是有效的，但对于第二张，第三张......它没有。它在 XValues 或 Values 中发出错误 1004“应用程序定义或对象定义错误”。我也注意到如果我介绍
range("K2")
outside the with block I get an error in the second, third .... sheets but not in the first one.
在 with 块之外，我在第二个、第三个 .... 工作表中出现错误，但在第一个中没有。
Any advise is most welcome
任何建议都是最受欢迎的
Regards
问候
Jo?o
乔?
回答by GSerg
- Qualify your
Ranges with sheets they belong to. - Don't use
SelectorActiveSheet. - Don't build up the string address. Use ranges directly.
Range用它们所属的表来限定你的s。- 不要使用
Select或ActiveSheet。 - 不要建立字符串地址。直接使用范围。
Dim CurSheet As Worksheet, cht As ChartObject
Dim chtChart As Chart, srsNew As Series
...
Set CurSheet = Worksheets(i)
For Each cht In CurSheet.ChartObjects
cht.Delete
Next
'create chart
Set chtChart = CurSheet.ChartObjects.Add(Left:=75, Width:=300, Top:=75, Height:=300).Chart
With chtChart
.ChartType = xlXYScatterSmooth
Do While .SeriesCollection.Count <> 0
.SeriesCollection(1).Delete
Loop
Set srsNew = .SeriesCollection.NewSeries
With srsNew
.XValues = CurSheet.Range(CurSheet.Range("k2"), CurSheet.Range("k2").End(xlDown))
.Values = CurSheet.Range(CurSheet.Range("l2"), CurSheet.Range("l2").End(xlDown))
End With
End With
回答by deusxmach1na
Maybe it would help if you fully qualified those Ranges like this:
如果您像这样完全限定这些范围，也许会有所帮助：
With srsNew
.XValues = "='" & Sheets(i).Name & "'!" & _
Sheets(i).Range(Sheets(i).Range("K2"), Sheets(i).Range("k2").End(xlDown)).Address
.Values = "='" & Sheets(i).Name & "'!" & _
Sheets(i).Range(Sheets(i).Range("l2"), Sheets(i).Range("l2").End(xlDown)).Address
End With
