ExcelBanter - Solver Issues

Hi, This might solve(!) your problem. It allows you to specify two input
cells (c1 and c2), and the output cell (op) in which the result of your
formula / function resides. The xmin and xmax specify the range of values
to test for both c1 to c2, stepped by value xstep.

It finds the minimum value (only one instance as there maybe more!) and
places the values in c1 and c2. It ignores divisions by zero and other
errors arising from the formula as c1 and c2 are progressively changed.

I have tried it on a number of different functions but I cannot say it is
bomb proof!

Sub Minimiser()
Dim c1 As Range, c2 As Range, op As Range
Dim xmin As Double, xmax As Double, xstep As Double
Dim r1 As Double, r2 As Double, res As Double
Dim x1 As Double, x2 As Double

' set range c1 = Cell 1; c2 = Cell 2; op = Resultant
Set c1 = Range("A1")
Set c2 = Range("A2")
Set op = Range("C1")

' set min and max values for range and step
xmin = -1
xmax = 1
xstep = 0.025

' process data
Application.ScreenUpdating = False
c1 = xmin: c2 = xmin
If Not IsError(op.Value) Then res = op.Value
For r1 = xmin To xmax Step xstep
r1 = Round(r1, 3): c1.Value = r1
For r2 = xmin To xmax Step xstep
r2 = Round(r2, 3): c2.Value = r2
'MsgBox "r1: " & Round(r1, 3) & " r2: " & Round(r2, 3)
If Not IsError(op.Value) Then
If op.Value < res Then
res = op.Value
x1 = r1: x2 = r2
End If
End If
Next r2
Next
Application.ScreenUpdating = True
' set cell1 and cell 2 to values that minmises op
c1.Value = x1
c2.Value = x2
MsgBox "Minimised Values: c1= " & x1 & ", c2= " & x2
End Sub

--
Cheers
Nigel

"speyry" wrote in
message ...

Its official: I can't get the solver to function properly in Excel 2000.
Soooo,
I know that is is possible, and not at all to difficult if you know
how to program fairly well. Unfortunately, I am not the best
programmer - nor do I have the time. I want:

Code that will act as a solver (within a macro) but far simpler. I
basically need to vary the values of two cells in increments of .025
from -1 to 1 (for each cell) and then find the combination that
minimizes my target cell after the effected cells change. Its *solver*
without the precision or the range or output or anything else. However,
it will work quite well for my application.

PLEASE HELP.

Thanks a billion in advance.

--
speyry
------------------------------------------------------------------------
speyry's Profile:
http://www.excelforum.com/member.php...o&userid=25128
View this thread: http://www.excelforum.com/showthread...hreadid=387656