Question about macro and solver

[plahen]

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

 

[tusharm]

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]

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]

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."

 

[plahen]

Thank you very much. It works great.

 

[tusharm]

You are welcome.