Automatic goal seek 3

[quark]

I have prepared a spreadsheet to calculate psychrometric properties. This requires solving two implicit equations when the inputs are temperature and RH. Presently, I am using goal seek function. Is there a way to activate the goal seek function without manually doing it every time, when the inputs are changed?

I am using goal seek for the following case.

Cell C7 is assume value and cell C8 is calculated value. I am using goal seek in cell F7 (which is C8 minus C7) to Set cell:F7 To value:0 By changing cell:C7

 

[quark]

It would be great if I can get suggestions beyond thread770-162709 and threads mentioned there.

 

[corus]

Use a command button and assign a macro to it. Best way is to record the steps you use into a macro.

corus

 

[handleman]

The code below will run goal seek any time the worksheet is changed by the user. It should not add much to sheet processing time if the changed cells are not input cells that affect this calculation, because it should already be at the goal. If, for some reason, you only want to Goal Seek when certain cells are changed, you will have to test the value of Target to see if any cells of interest are contained in it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bSuccess As Boolean

    On Error Resume Next
    bSuccess = Range("F7").GoalSeek(0, Range("C7"))
    On Error GoTo 0
    If Not bSuccess Then
        MsgBox "Goal Seek Failed for Cell ""C7""!"
    End If

End Sub

-handleman, CSWP (The new, easy test)

 

[GregLocock]

Another approach is to use circular cell references and switch iteration on, if you want to avoid vba. However handleman's approach is much easier to understand and doesn't get into horrible loops when errors occur.



Cheers

Greg Locock

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[quark]

Excellent handleman! That worked perfectly. The cells in goal seek are not input cells.

Corus,

Thanks, I did it yesterday after your suggestion, an easy and nice solution. The only problem was that the spreadsheet has two types of inputs with user preference. Providing a command button only to one set of calculations and running the other set automatically didn't look good. Nevertheless, you have an excellent idea for me to use in future.

Greg,

Tried the iteration method but was getting wrong values. Each parameter calculation has more than two if loops (depending upon the valid temperature range) and are implicit. Iteration might have worked but I didn't have enough patience to go through the formulae one by one and removing the circular reference.

Thanks to you all, once again.