如何在Excel用户功能中使用目标搜索功能? [英] How to use goal seek function in Excel user function?

问题描述

在MS excel中有使用目标搜索的方法，如以下代码所示.效果很好.

There is method to use goal seek in MS excel like follows code. It works fine.

   Sub GSeek() 
      With Worksheets("Sheet1") 
             .Range("H18").GoalSeek _  
              Goal:=.Range("H21").Value, _ 
               ChangingCell:=.Range("G18")
     End With 
   End Sub

我想使用一个函数来进行目标查找，如下所示.

And I would like to use a function to do the goal seek like follows.

  Function fSeek(a As Range, b As Range, c As Range)
     a.GoalSeek Goal:=b.Value, ChangingCell:=c
     fSeek = "Ok"
   End Function

但是，代码可以正常运行，并且在c范围内未找到答案.我在哪里错了?

However, the code work peacefully and no answer was got in the range c. Where am I wrong?

推荐答案

谢谢大家回答我.很久以后，我再次在这里找到了我的问题. 我碰巧通过事件找到了这个问题的解决方案.

Thank you all for answering me. I found my question again here after a long time. I happened to find the solution to the this question by using the event.

在Microsoft Excel Object-Sheet1(Sheet1)中，我们只能编写以下代码:

In the Microsoft Excel Object-Sheet1(Sheet1), we can only write the following code:

 '----------------------------------------------
 ' Goal seeking when the worksheet changes.
 '----------------------------------------------
 ' Here we want to do goal seek for Range("H18") 
 '  with the Goal cell as  Range("H21") 
 '  and  the changing cell as Range("G18").
 '
 Private Sub Worksheet_Change(ByVal Target As Range)
     Range("H18").GoalSeek Goal:=Range("H21"), ChangingCell:=Range("G18")
 End Sub

很酷吗?

云

这篇关于如何在Excel用户功能中使用目标搜索功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！