Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros involving SOLVER... function
My intial problem was to solve a simultaneous equation using excel. You can do this using Tools Solver... Now I wanted to create a macro so everytime I change my constraints< I can run the macro which would in turn run the commands in Solver... I did this BUT when I actually ran the macro again I got this error: Compile Error: Sub or Function not defined. Macro: Sub last() ' ' last Macro ' Macro recorded 19.04.2006 by Lewis Holland ' ' SolverOk MaxMinVal:=1, ValueOf:="0", ByChange:="$D$9:$D$10" SolverAdd CellRef:="$A$9:$A$10", Relation:=2, FormulaText:="$B$9:$B$10" SolverOk MaxMinVal:=1, ValueOf:="0", ByChange:="$D$9:$D$10" SolverSolve End Sub Can anyone help solve this? Can you record a macro which actually uses this Solver.. function? -- Pixies ------------------------------------------------------------------------ Pixies's Profile: http://www.excelforum.com/member.php...o&userid=33642 View this thread: http://www.excelforum.com/showthread...hreadid=534142 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros involving SOLVER... function
In the vba editor, go to Tools | Reference, and set a reference to "Solver."
In your code, I believe your left out a reference to the Target Cell (ie SetCell). If you keep calling Solver, you will eventually have too many redundant constraints. I find it best to start from scratch with SolverReset. SolverReset SolverOk SetCell:="A1", MaxMinVal:=1, ByChange:="D9:D10" SolverAdd CellRef:="A9:A10", Relation:=2, FormulaText:="B9:B10" SolverSolve True -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Pixies" wrote in message ... My intial problem was to solve a simultaneous equation using excel. You can do this using Tools Solver... Now I wanted to create a macro so everytime I change my constraints< I can run the macro which would in turn run the commands in Solver... I did this BUT when I actually ran the macro again I got this error: Compile Error: Sub or Function not defined. Macro: Sub last() ' ' last Macro ' Macro recorded 19.04.2006 by Lewis Holland ' ' SolverOk MaxMinVal:=1, ValueOf:="0", ByChange:="$D$9:$D$10" SolverAdd CellRef:="$A$9:$A$10", Relation:=2, FormulaText:="$B$9:$B$10" SolverOk MaxMinVal:=1, ValueOf:="0", ByChange:="$D$9:$D$10" SolverSolve End Sub Can anyone help solve this? Can you record a macro which actually uses this Solver.. function? -- Pixies ------------------------------------------------------------------------ Pixies's Profile: http://www.excelforum.com/member.php...o&userid=33642 View this thread: http://www.excelforum.com/showthread...hreadid=534142 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros involving SOLVER... function
Ok I added the reference & tried your other suggestions too. The Macro seems to be running, but I don't get a solution. Better explanation of my problem: A B D 8 Eqn Const. Solution 9 0 62% 10 0 38% A/B/D Are columns, 8,9,10 the rows. A9: Formula A (Depending on d9 & 10) A10: Formula B (Depending on d9 & 10) Formula A = B9 Formula B = B10 As far as I know I had no reason to enter anything into the SetCell Section. I edited your suggestion to the following but still no sucess: SolverReset SolverOk SetCell:="", MaxMinVal:=1, ValueOf:="0", ByChange:="D9:D10" SolverAdd CellRef:="A9:A10", Relation:=2, FormulaText:="B9:B10" SolverSolve True End Sub -- Pixies ------------------------------------------------------------------------ Pixies's Profile: http://www.excelforum.com/member.php...o&userid=33642 View this thread: http://www.excelforum.com/showthread...hreadid=534142 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros involving SOLVER... function
As far as I know I had no reason to enter anything into the SetCell
Section. I don't think Solver knows by default which cell you are trying to maximize. As a side note, when Maximizing, Solver ignores the ValueOf:=0. -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Pixies" wrote in message ... Ok I added the reference & tried your other suggestions too. The Macro seems to be running, but I don't get a solution. Better explanation of my problem: A B D 8 Eqn Const. Solution 9 0 62% 10 0 38% A/B/D Are columns, 8,9,10 the rows. A9: Formula A (Depending on d9 & 10) A10: Formula B (Depending on d9 & 10) Formula A = B9 Formula B = B10 As far as I know I had no reason to enter anything into the SetCell Section. I edited your suggestion to the following but still no sucess: SolverReset SolverOk SetCell:="", MaxMinVal:=1, ValueOf:="0", ByChange:="D9:D10" SolverAdd CellRef:="A9:A10", Relation:=2, FormulaText:="B9:B10" SolverSolve True End Sub -- Pixies ------------------------------------------------------------------------ Pixies's Profile: http://www.excelforum.com/member.php...o&userid=33642 View this thread: http://www.excelforum.com/showthread...hreadid=534142 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros involving SOLVER... function
Ok. I tried doing it exactly as you said. But I didn't get any output. Any reason for that? (For extra info) When I do the same process manually: I am ignoring top section. I get an output in D9:D10, which is correct for the 2 simultaneous equations I have got. Excuse my ignorance with Macro's - it's my first time. Althought I have done other programming etc. -- Pixies ------------------------------------------------------------------------ Pixies's Profile: http://www.excelforum.com/member.php...o&userid=33642 View this thread: http://www.excelforum.com/showthread...hreadid=534142 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros involving SOLVER... function
Can you help me with the problem? -- Pixies ------------------------------------------------------------------------ Pixies's Profile: http://www.excelforum.com/member.php...o&userid=33642 View this thread: http://www.excelforum.com/showthread...hreadid=534142 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sub list and rum macros in the function | New Users to Excel | |||
Solver does not work from Macros | Setting up and Configuration of Excel | |||
Solver problem where 'IF Function' is bad | Excel Discussion (Misc queries) | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |