PDA

View Full Version : How to make a solver VBA code in a dynamic range



bbuk9999
05-04-2016, 07:47 AM
Hi all,
I want to do the solver VBA code to be used in a dynamic (generic ) range. Any help is appreciated.

Kenneth Hobs
05-04-2016, 09:32 AM
If you have a named range, then just use it like Range("MyNamedRange") or Range("MyNamedRange").Address or such.

e.g. solver code

' Solver, http://support.microsoft.com/kb/843304' SolverOK, http://msdn.microsoft.com/en-us/library/office/aa272367%28v=office.10%29.aspx


' Add the Solver Reference:
' In the VBE, Tools > References, paste this in the File name box:
' C:\Program Files\Microsoft Office\OFFICExx\Library\SOLVER
' after replacing xx with your version of Excel (e.g., 11 for Excel 2003, 12 for Excel 2007, 14 fir Excel 2010).
' Change the file type dropdown to *.*, and pick SOLVER.XLA or SOLVER.XLAM
' You can't set a reference when code execution is suspended, so press the reset button (or do Tools > Reset), and then try.


' http://www.excelforum.com/excel-programming-vba-macros/952779-automated-solver.html
Sub Solve()
Dim r As Long, i As Long

r = 100

'Range("C1").Formula = "=(A1^2 + B1^1.5 - 0.7*A1*B1-0.19)^2"
' |X|^2.5 + |Y|^1.5 - 0.02*X*Y - 1
Range("C1").Formula = "=(abs(a1)^2.5 + abs(b1)^1.5 - 0.02*abs(a1)*abs(b1) - 1)^2"
Range("C1").Copy Range("C2:C" & r)

For i = 1 To r
Range("A" & i).Value2 = i
'Range("H" & r).GoalSeek Goal:=Range("I" & r).Value, ChangingCell:=Range("A" & r)
SolverOk SetCell:="C$" & i, MaxMinVal:=3, ValueOf:=0, ByChange:="$B$" & i, Engine:=1 _
, EngineDesc:="GRG Nonlinear"
SolverSolve userfinish:=True
Next i
End Sub