目标寻求宏与目标作为一个公式-GoalSeekMacrowithGoalasaFormula

4  

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单元格包含值而不是公式,或者如果ChangingCell包含公式而不是值或什么都不包含,则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,这是我寻求的目标。

1  

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")