How to write own goal seek function which took parameters from excelsheet?

[Wille]

Hi All,

I think this guestion is quite easy but I can't find any path to solve
issue . Idea is to use this function as excel normal functions e.g.
SUM().

My code looks like this:
********************************************************************************
Public Function Own_solver(Target As Range, Variable As Range)

If Abs(Target.Value) > 0.01 Then

Target.GoalSeek Goal:=0, ChangingCell:=Variable

End If
End Function
*********************************************************************************

Where "Targer" and "Variable" are variables which are given in excel
sheet. How I should change structure of my code to pass cell
addresses to code? At this moment only values are passed and function
fails.

Thanks in advance,

BR

 

[Gary''s Student]

The function is failing because you are not returning anything from it. You
need a line like:

Own_solver = "some value"

 

[Wille]

Hi, Gary

I added return value to code but still it not work. Any other ideas to
fix the code ?

Thanks,

 

[Gary''s Student]

Functions cannot change cell values other than to supply a return. If Goal
Seek needs to change a cell's value, then a function may not be the suitable
solution. You may need a macro instead.


Consider using a worksheet event macro (Change or Calculate) to automate
Solver instead.

 

[Wille]

Thanks a lot Gary,

My solution is followed:

Private Sub Worksheet_Calculate()
SEEKGOAL
End Sub

Sub SEEKGOAL()
'Range(Target).GoalSeek Goal:=0, ChangingCell:=Range(Variable) \\User
must manually add enough lines
Range("A1").GoalSeek Goal:=0, ChangingCell:=Range("B2")

End Sub

 

[Jon Peltier]

Not tested here, but did you try Target.Address and Variable.Address?

- Jon