Question about macro and solver

plahen

New Member
Joined
Nov 3, 2004
Messages
17
I need someones help!!!

I have a spreadsheet where I need to 2 cells be equal to each other. Yeah sounds easy enough, but the first cell is set b/c it was calc'd form other cells. The third cell is a function of the 2nd cell. So everytime the 2nd cell is changed, the third cell gets a new value. I want to set the 2nd cell to the value that will make the 3rd cell equivilant to the first cell. I also need to use solver to get the exact number fo make the first and third cell equal. I also want to set a hot key to make this function work.

Have i thoroughly confused everyone?

If you think you can help, post you replys and email me at plahen@yahoo.com, so I can get you the spreadsheet.

Thanks
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,026
Assuming there is a good reason why you have to use Solver (HW, perhaps?), what have you done so far and where are you stuck? And, no, this is not an invitation for you to send me your workbook. To the extent possible, let's keep it in the public domain so others can contribute their ideas.
 

plahen

New Member
Joined
Nov 3, 2004
Messages
17
re

Ok. I tried to record my macro, but when I did that and tried to run it using my hot key, I get a "compile error - Sub or funct not defined"


This is what shows up in the MVB window

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/18/2006
'
' Keyboard Shortcut: Ctrl+b
'
SolverOk SetCell:="$AF$14", MaxMinVal:=2, ValueOf:="0", ByChange:="$Z$14"
SolverDelete CellRef:="$U$13", Relation:=2, FormulaText:="$AF$13"
SolverAdd CellRef:="$U$14", Relation:=2, FormulaText:="$AF$14"
SolverOk SetCell:="$AF$14", MaxMinVal:=2, ValueOf:="0", ByChange:="$Z$14"
SolverSolve
End Sub
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,026
If you look up XL help for any of the Solver related subroutines, you will find the paragraph reproduced below:
"Before you use this function, you must establish a reference to the Solver add-in. With a Visual Basic module active, click References on the Tools menu, and then select the Solver.xla check box under Available References. If Solver.xla doesn't appear under Available References, click Browse and open Solver.xla in the \Office\Library\Solver\ subfolder."
 

Watch MrExcel Video

Forum statistics

Threads
1,114,093
Messages
5,545,919
Members
410,712
Latest member
jhgeorge
Top