目标寻求具有目标的宏作为公式 [英] Goal Seek Macro with Goal as a Formula
问题描述
m试图通过创建目标追踪宏来解决Excel中的循环引用问题 - 基本上
-
S3 = M + S2
, -
S3 = L * G
。
我想通过更改M来寻求S3来等于 L * G
所以,我把
-
单元格中的L * G
H32 , -
S3
H18 , -
M
F18 , -
S2
/ li>
这是我迄今收集的。
Sub GoalSeek()
Dim x As Integer
x = Range(H32)。 b $ b Range(H18)。GoalSeek目标:= x,ChangingCell:= Range(G18)
End Sub
我得到一个参考无效的错误,想法?
谢谢!
如果GoalSeek单元格包含一个值而不是一个值,GoalSeek将抛出无效的参考错误公式或者如果ChangingCell包含一个公式而不是一个值或者没有。
GoalSeek单元格必须包含一个直接或间接指向ChangingCell的公式;如果公式没有以某种方式提及ChangingCell,GoalSeek可能不会收敛于答案或可能产生一个无意义的答案。
我用一个不同的GoalSeek公式比你的(我不清楚一些术语是指细胞还是值)。
对于测试,我设置:
GoalSeek单元格H18 =(G18 ^ 3)+(3 * G18 ^ 2)+6
目标单元格H32 = 11
ChangeCell G18 = 0
代码是:
Sub GSeek()
而代码产生1.1038的(正确的)答案,G18的值为H18的公式产生的值为11,目标是寻求。
与工作表(Sheet1)
.Range(H18)。GoalSeek _
目标:=。Range(H32)。Value,_
ChangingCell:=。Range(G18)
End with
End Sub
I'm grasping at straws here, so any help would be great (ie. I have no idea what I'm doing with VBA).
I'm trying to solve a circular reference problem in Excel by creating a goal seek macro - basically
S3 = M + S2
,S3 = L * G
.I want to goal seek S3 to equal
L * G
by changing M.So, I've put
L * G
in cellH32
,S3
inH18
,M
inG18
,S2
inF18
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!
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.
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.
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).
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.
这篇关于目标寻求具有目标的宏作为公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!