vba 在单元格范围内自动寻找目标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16240176/
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
Automatic Goal Seek Over Range of Cells
提问by user2324441
I want to apply goal seek across several rows when there is a change to any cell in the work sheet. I want to apply this from row 7 to row 11. The first problem I have is that excel is crashing each time I run this. I am just starting to learn VBA so any help is much apreciated. Thank you!
当工作表中的任何单元格发生更改时，我想在多行中应用目标搜索。我想将它从第 7 行应用到第 11 行。我遇到的第一个问题是每次运行它时 excel 都会崩溃。我刚刚开始学习 VBA，因此非常感谢任何帮助。谢谢！
My code is below:
我的代码如下：
Option Explicit
Private Sub Worksheet_Calculate()
CheckGoalSeek
End Sub
Private Sub CheckGoalSeek()
Range("T7").GoalSeek Goal:=0, ChangingCell:=Range("V7")
End Sub
回答by chuff
You appear to be triggering an infinite loop: worksheet calculation -> goal seek calculation -> worksheet calculation -> ...
您似乎正在触发一个无限循环：工作表计算 -> 目标搜索计算 -> 工作表计算 -> ...
One option is to change the event that triggers the goal seek.
一种选择是更改触发目标搜索的事件。
I would recommend the Worksheet_Change event. The event code would be the same except for the sub declaration, which would be Private Sub Worksheet_Change(ByVal Target As Range).
我会推荐 Worksheet_Change 事件。除了子声明之外，事件代码将是相同的，即 Private Sub Worksheet_Change(ByVal Target As Range).
A simple Forloop will perform the Goal Seek on the different rows:
一个简单的For循环将在不同的行上执行 Goal Seek：
Option Explicit
Private Sub CheckGoalSeek()
Dim i as Long
For i = 7 to 11
Range("T"& i).GoalSeek Goal:=0, ChangingCell:=Range("V"& i)
Next
End Sub
