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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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,029
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."
 

Forum statistics

Threads
1,144,163
Messages
5,722,853
Members
422,461
Latest member
kelleys315

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top