Excel 2010 VBA - 使用由变量定义的范围更新图表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13072096/
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 2010 VBA - Update Graph using range defined by variable
提问by user1774673
I have an Excel sheet that is updating daily. I am trying to automatically update a graph with the new data (1 row) that is added daily.
我有一个每天更新的 Excel 工作表。我正在尝试使用每天添加的新数据（1 行）自动更新图表。
So far I have:
到目前为止，我有：
Sub UpdateGraphs()
Dim latestRow As Integer
Sheets("DailyJourneyProcessing").Select
Range("A500").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value <> "" Then
ActiveCell.Offset(1, 0).Select
End If
Loop
ActiveCell.Offset(-1, 0).Select
Application.CutCopyMode = False
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
latestRow = ActiveCell.row
Dim str1 As String
Dim rng1 As Range
str1 = "=DailyJourneyProcessing!$F0:$F$" & latestRow
Set rng1 = Range(str1)
Debug.Print "Got this far..."
Set ActiveChart.SeriesCollection(1).Values = Range(str1)
I know that this looks like I simply copy the previous row but the formula's included take car of the changes in data.
我知道这看起来就像我只是简单地复制了前一行，但包含的公式会影响数据的变化。
The Integer / row at the moment is around 520, so I want to do:
目前的 Integer / row 大约是 520，所以我想做：
ActiveChart.SeriesCollection(1).Values = "=DailyJourneyProcessing!$F0:$F0"
Where the row number changes daily. This is one of about 20 range updates I need to automate, but once I have solved one the others should be the same.
行号每天变化的地方。这是我需要自动化的大约 20 个范围更新之一，但是一旦我解决了一个，其他的应该是相同的。
I have tried everything I can find online, but nothing quite worked.
我已经尝试了我可以在网上找到的所有内容，但没有任何效果。
At the moment, I get a run-time error 91: Object or With block variable not set.
目前，我收到运行时错误 91：未设置对象或块变量。
Any help would be appreciated.
任何帮助，将不胜感激。
采纳答案by Scott Holtzman
There is actually no need for VBA to accomplish this. You will find the method in this linkmuch easier to manage and maintain than VBA code. Also, its really best not to use VBA when you don't have to!
实际上不需要 VBA 来完成这个。您会发现此链接中的方法比 VBA 代码更易于管理和维护。此外，最好不要在不需要时使用 VBA！
However, so that you can see a more efficient way to code what you were trying to do, I've offered the code below. It very well may need some tweaks to fit your actual data set.
但是，为了您可以看到一种更有效的方式来编写您想要做的事情，我提供了下面的代码。它很可能需要一些调整以适应您的实际数据集。
Sub UpdateGraphs()
Dim wks As Worksheet, rng1 As Range
Dim latestRow As Long ' changed to long to handle rows over 32,000 (whatever number Integer stops at)
Set wks = Sheets("DailyJourneyProcessing")
With wks
latestRow = .Range("F" & .Rows.Count).End(xlUp).Row
str1 = "=DailyJourneyProcessing!$F0:$F$" & latestRow
Set rng1 = Range(str1)
Dim myChart As Chart
Set myChart = .ChartObjects("myChartName")
myChart.SeriesCollection(1).Values = rng1
End With
End Sub
