VBA GoalSeek Comparion

B3nnyF · Jan 11, 2012

I'm relatively new to excel and have been having an issue with some vba in this model i'm working on....

Essentially I want to run 3 goalseeks on the same dynamic variable (L63) and return the minimum value goalseek finds.

The following are the procedures for each goalseek:

 Sub DSCRof1()
Sheets("CFADS-A").Range("J51").GoalSeek Goal:=1.402, ChangingCell:=Range( _
"L63")
End Sub
Sub DSCRof2()
Sheets("CFADS-A").Range("J51").GoalSeek Goal:=1.002, ChangingCell:=Range( _
"L63")
End Sub
Sub Debt85Percent()
Sheets("Summary").Range("F10").GoalSeek Goal:=0.85, ChangingCell:=Range( _
"L63")
End Sub

How can Excel return the minimum value to cell, say, L63?

B3nnyF · Jan 11, 2012

Also I was wondering how you can return goalseek's output to a cell other than then one used for the dynamic variable.

Thanks in advance

tusharm · Jan 11, 2012

Compare the value in L63 after the goalseek completes with a previous stored min. value and retain the min. of the comparison.

Code:

Sub DSCR()
    Dim MinVal As Double
    Sheets("CFADS-A").Range("J51").GoalSeek Goal:=1.402, ChangingCell:=Range("L63")
    MinVal = Range("L63").Value
    Sheets("CFADS-A").Range("J51").GoalSeek Goal:=1.002, ChangingCell:=Range("L63")
    If MinVal > Range("L63").Value Then MinVal = Range("l63").Value
    Sheets("Summary").Range("F10").GoalSeek Goal:=0.85, ChangingCell:=Range("L63")
    If MinVal > Range("L63").Value Then MinVal = Range("l63").Value
    End Sub

To put the value of the variable in some other cell, just copy + paste. If you want to do it programmatically and don't know how, get the code with the help of the macro recorder.

B3nnyF said:
I'm relatively new to excel and have been having an issue with some vba in this model i'm working on....

Essentially I want to run 3 goalseeks on the same dynamic variable (L63) and return the minimum value goalseek finds.

The following are the procedures for each goalseek:

 Sub DSCRof1()
Sheets("CFADS-A").Range("J51").GoalSeek Goal:=1.402, ChangingCell:=Range( _
"L63")
End Sub
Sub DSCRof2()
Sheets("CFADS-A").Range("J51").GoalSeek Goal:=1.002, ChangingCell:=Range( _
"L63")
End Sub
Sub Debt85Percent()
Sheets("Summary").Range("F10").GoalSeek Goal:=0.85, ChangingCell:=Range( _
"L63")
End Sub

How can Excel return the minimum value to cell, say, L63?

B3nnyF · Jan 12, 2012

Thanks I get the logic but it's not getting the minimum value (i know the first goalseek is the minimum but its returning the value for the third). Suppose I just ran one of the goal seeks. Can I return the output value to another cell without changing the dynamic variable?

VBA GoalSeek Comparion

B3nnyF

New Member

Excel Facts

B3nnyF

New Member

tusharm

MrExcel MVP

B3nnyF

New Member

Similar threads

Forum statistics

Share this page

VBA GoalSeek Comparion

B3nnyF

New Member

Excel Facts

B3nnyF

New Member

tusharm

MrExcel MVP

B3nnyF

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock