Excel VBA 图表 -> 在图表中使用“单元格”选择范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15838191/
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 VBA Charts -> range select using "Cells" in within Charts
提问by PeterKallus
I'm trying to modify an existing VBA code (Excel) which makes Chart graphs, and make it more flexible.
我正在尝试修改现有的 VBA 代码 (Excel)，它可以制作图表，并使其更加灵活。
I know that the following pieces of code do basically the same thing:
我知道以下几段代码基本上做了同样的事情：
Range(Cells(12, 2), Cells(15, 2)).Select
is more or less identical to:
或多或少等同于：
Range("B12:B15").Select
My goal was to have a Graph, which is representing the flexible count of rows.
我的目标是有一个 Graph，它代表灵活的行数。
So I have changed the existing code:
所以我改变了现有的代码：
ActiveChart.SetSourceData Source:=Sheets("Log-Data").Range("B12:B200"), PlotBy:=xlColumns
to
到
Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
ActiveChart.SetSourceData Source:=Sheets("Log-Data").Range(Cells(12, 2), Cells(LastRow, 2)), PlotBy:=xlColumns
Now, whenever I execute the code, I receive:
现在，每当我执行代码时，我都会收到：
Run-time error '1004': Application-defined or object-defined error
运行时错误“1004”：应用程序定义或对象定义错误
The LastRowvariable is not the problem: I have the same result if I replace it with 200.
该LastRow变量是没有问题的：我也有同样的结果，如果我用200取代它。
What am I doing wrong?
我究竟做错了什么？
Cheers
干杯
Peter
彼得
回答by 273K Kool
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
and
和
LastRow = ActiveSheet.UsedRange.Rows.Count
LastRow = ActiveSheet.UsedRange.Rows.Count
are the same.
是相同的。
As far as the actual error goes, it looks as if you have no active chart object when the code is run. So ActiveChart.returns an object error.
就实际错误而言，在运行代码时，您似乎没有活动的图表对象。所以ActiveChart.返回一个对象错误。
Try this:
尝试这个：
Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Sheets("Log-Data").Range(Cells(12, 2), Cells(LastRow, 2)), PlotBy:=xlColumns
回答by user3962941
I tried the following and it works (adding a period before Cells word)
我尝试了以下操作并且它有效（在 Cells word 之前添加一个句点）
Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
With Sheets("Log-Data")
Set Myrange = .Range(.Cells(12, 2), .Cells(LastRow, 2))
End With
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Myrange, PlotBy:=xlColumns
回答by Sanjay Narayan
Try below and let me know
试试下面，让我知道
Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
With Sheets("Log-Data")
Set Myrange = .Range(Cells(12, 2), Cells(LastRow, 2))
End With
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Myrange, PlotBy:=xlColumns
