vba 以目标为公式的目标寻求宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18139322/
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
Goal Seek Macro with Goal as a Formula
提问by Natalie
I'm grasping at straws here, so any help would be great (ie. I have no idea what I'm doing with VBA).
我在这里抓住了稻草，所以任何帮助都会很棒（即。我不知道我在用 VBA 做什么）。
I'm trying to solve a circular reference problem in Excel by creating a goal seek macro - basically
我正在尝试通过创建目标查找宏来解决 Excel 中的循环引用问题 - 基本上
S3 = M + S2,S3 = L * G.
S3 = M + S2,S3 = L * G.
I want to goal seek S3 to equal L * Gby changing M.
我想L * G通过改变 M来寻求 S3 相等。
So, I've put
所以，我已经把
L * Gin cellH32,S3inH18,MinG18,S2inF18
L * G在细胞中H32，S3在H18，M在G18，S2在F18
and this is what I've gathered so far.
这是我到目前为止收集的。
Sub GoalSeek()
Dim x As Integer
x = Range("H32").Value
Range("H18").GoalSeek Goal:=x, ChangingCell:=Range("G18")
End Sub
I'm getting a "Reference is not valid" error, ideas? Thanks!
我收到“参考无效”错误，想法？谢谢！
回答by chuff
GoalSeek will throw an "Invalid Reference" error if the GoalSeek cell contains a value rather than a formula or if the ChangingCell contains a formula instead of a value or nothing.
如果 GoalSeek 单元格包含值而不是公式，或者如果更改单元格包含公式而不是值或什么都没有，则 GoalSeek 将抛出“无效引用”错误。
The GoalSeek cell must contain a formula that refers directly or indirectly to the ChangingCell; if the formula doesn't refer to the ChangingCell in some way, GoalSeek either may not converge to an answer or may produce a nonsensical answer.
GoalSeek 单元格必须包含一个直接或间接引用ChangingCell 的公式；如果公式没有以某种方式引用ChangingCell，则GoalSeek 可能不会收敛到答案或可能会产生无意义的答案。
I tested your code with a different GoalSeek formula than yours (I wasn't quite clear whether some of the terms referred to cells or values).
我使用与您不同的 GoalSeek 公式测试了您的代码（我不太清楚某些术语是指单元格还是值）。
For the test, I set:
为了测试，我设置：
the GoalSeek cell H18 = (G18^3)+(3*G18^2)+6
the Goal cell H32 = 11
the ChangingCell G18 = 0
The code was:
代码是：
Sub GSeek()
With Worksheets("Sheet1")
.Range("H18").GoalSeek _
Goal:=.Range("H32").Value, _
ChangingCell:=.Range("G18")
End With
End Sub
And the code produced the (correct) answer of 1.1038, the value of G18 at which the formula in H18 produces the value of 11, the goal I was seeking.
代码产生了（正确的）答案 1.1038，即 G18 的值，H18 中的公式产生的值为 11，这是我寻求的目标。
回答by ARich
I think your issue is that Range("H18")doesn't contain a formula. Also, you could make your code more efficient by eliminating x. Instead, change your code to
我认为您的问题是Range("H18")不包含公式。此外，您可以通过消除x. 相反，将您的代码更改为
Range("H18").GoalSeek Goal:=Range("H32").Value, ChangingCell:=Range("G18")
