Goal seek with two variables

[simoncpage]

Hi All, I am using the following loop to work out a goal seek scenario where by it is factored by two different values:

Its quite long and hence a bit ugly but has anyone got any ideas how to make this speed things up?

--------------------------------------------------------
If (Range(&quot;Perc&quot.Value - Range(&quot;base&quot.Value) < Range(&quot;percseek&quot.Value Then

Do
Range(&quot;marginsn&quot.Value = Range(&quot;marginsn&quot.Value - 0.0005
Range(&quot;year&quot.Value = Term
Amortise
SubAmortise
If Range(&quot;Perc&quot.Value - Range(&quot;base&quot.Value > Range(&quot;percseek&quot.Value Then
Exit Do
Else
End If

Loop

Do
Range(&quot;marginsn&quot.Value = Range(&quot;marginsn&quot.Value + 0.0001
Range(&quot;year&quot.Value = Term
Amortise
SubAmortise
If Range(&quot;Perc&quot.Value - Range(&quot;base&quot.Value < Range(&quot;percseek&quot.Value Then
Exit Do
Else
End If

Loop

Do
Range(&quot;marginsn&quot.Value = Range(&quot;marginsn&quot.Value - 0.00005
Range(&quot;year&quot.Value = Term
Amortise
SubAmortise
If Range(&quot;Perc&quot.Value - Range(&quot;base&quot.Value > Range(&quot;percseek&quot.Value Then
Exit Do
Else
End If

Loop

Do
Range(&quot;marginsn&quot.Value = Range(&quot;marginsn&quot.Value + 0.00001
Range(&quot;year&quot.Value = Term
Amortise
SubAmortise
If Range(&quot;Perc&quot.Value - Range(&quot;base&quot.Value < Range(&quot;percseek&quot.Value Then
Exit Do
Else
End If

Loop

Do
Range(&quot;marginsn&quot.Value = Range(&quot;marginsn&quot.Value - 0.000005
Range(&quot;year&quot.Value = Term
Amortise
SubAmortise
If Range(&quot;Perc&quot.Value - Range(&quot;base&quot.Value > Range(&quot;percseek&quot.Value Then
Exit Do
Else
End If

Loop

Range(&quot;marginsn&quot.Value = Range(&quot;marginsn&quot.Value + 0.000005
Range(&quot;year2&quot.Value = Term

Else

Do
Range(&quot;marginsn&quot.Value = Range(&quot;marginsn&quot.Value + 0.0005
Range(&quot;year&quot.Value = Term
Amortise
SubAmortise
If Range(&quot;Perc&quot.Value - Range(&quot;base&quot.Value < Range(&quot;percseek&quot.Value Then
Exit Do
Else
End If

Loop

Do
Range(&quot;marginsn&quot.Value = Range(&quot;marginsn&quot.Value - 0.0001
Range(&quot;year&quot.Value = Term
Amortise
SubAmortise
If Range(&quot;Perc&quot.Value - Range(&quot;base&quot.Value > Range(&quot;percseek&quot.Value Then
Exit Do
Else
End If

Loop

Do
Range(&quot;marginsn&quot.Value = Range(&quot;marginsn&quot.Value + 0.00005
Range(&quot;year&quot.Value = Term
Amortise
SubAmortise
If Range(&quot;Perc&quot.Value - Range(&quot;base&quot.Value < Range(&quot;percseek&quot.Value Then
Exit Do
Else
End If

Loop

Do
Range(&quot;marginsn&quot.Value = Range(&quot;marginsn&quot.Value - 0.00001
Range(&quot;year&quot.Value = Term
Amortise
SubAmortise
If Range(&quot;Perc&quot.Value - Range(&quot;base&quot.Value > Range(&quot;percseek&quot.Value Then
Exit Do
Else
End If

Loop

Do
Range(&quot;marginsn&quot.Value = Range(&quot;marginsn&quot.Value + 0.000005
Range(&quot;year&quot.Value = Term
Amortise
SubAmortise
If Range(&quot;Perc&quot.Value - Range(&quot;base&quot.Value < Range(&quot;percseek&quot.Value Then
Exit Do
Else
End If

Loop

Range(&quot;marginsn&quot.Value = Range(&quot;marginsn&quot.Value - 0.000005
Range(&quot;year2&quot.Value = Term

End If
End Sub

----------------------------------------
Any help would be great thanks!

 

[cheerio]

Let me explain what I would do for a one dimensional goal seek and then you can see whether you can adapt.

The method can be described as a binary chop.

Suppose I want to know what value of x satisfies x * x * x - 3 = 0 (ie x cubed minus 3 = 0)

I have two values low and high.

I set low = 1 (1 cubed - 3 is -2)
I set high = 2 (2 cubed -3 is 5)

the answer is somewhere between 1 and 2

set test = half of (high + low) ie the mid point and feed test into the formula.

This gives a positive result so I replace high with test. If test had given a negative answer I would replace low with test.

Now the answer is still between low and high but the gap is half the size it was.

Repeat the above process until the gap is sufficiently close you can ignore and stop seeking.

A binary chop is very simple to code and will be quite fast.

Ken