Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver Issues
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scrolling issues | Excel Discussion (Misc queries) | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
need help for several issues | Excel Programming | |||
Resetting Solver Manually to Fix Solver Bug | Excel Programming | |||
Issues with solver | Excel Programming |