1

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

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

Bhargav Rao
  • 41,091
  • 27
  • 112
  • 129
Yun_cn
  • 31
  • 1
  • 3
  • 3
    User-Defined Functions (UDF) are not allowed to change other cells when invoked from a formula. – A.S.H Jul 12 '17 at 01:54
  • Thanks a lot for your reply. Are there any way to get the same goal? I really want to seek many goals in the same worksheet. – Yun_cn Jul 12 '17 at 02:40
  • You can do that, but via macros not via formulas. – A.S.H Jul 12 '17 at 03:37
  • But macros can not use parameters. Sub procedures with parameters are not show the macro list at all. – Yun_cn Jul 12 '17 at 04:23
  • 1
    You can always find a way to specify what you want to seek and with what parameters. If those are not a-priori known cells, you can use forms or `InputBox` (inside the macro) to let the user enter them. – A.S.H Jul 12 '17 at 05:23
  • I really appreciate your advice. I can now do the goal seek with a macro by using inputBox to select the cells as parameters. – Yun_cn Jul 12 '17 at 07:09

2 Answers2

1

I am writing an answer because it seems this question is resurging.

Bottom line is that User-Defined Functions (UDF) are not allowed to change other cells when invoked from a formula.

Solution: To automate godl-seeking, write a VBA macro, not a UDF.

A macro can automate goal-seeking on any range or set of cells. Since a macro does not have parameters, there are many ways to permit the user specify the set of cells to GoalSeek and the parameters, and to route these to the Range.GoalSeek method:

1- Design a dedicated UserForm

2- Use VBA's InputBox function, or possibly Excel's Application.InputBox fuction with parameter type:=8 to enter a range.

3- dedicate some cells on the worksheet where the user can enter the parameters.

A.S.H
  • 28,433
  • 5
  • 19
  • 45
  • Thank you very much. I have succeeded to create a dialog to do goal seeking as your suggestion. – Yun_cn Oct 27 '17 at 06:12
1

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.

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

Is it cool?

Yun

Yun_cn
  • 31
  • 1
  • 3