Run Solver with Macro Assigned to Button

Slazar

New Member
Joined
Aug 28, 2015
Messages
17
I have a solver below that sets the objective function equal to Project Raw Cost (inputted by the user). It then calculates the cost of each category subject to constraints.

I'm very new to VBA. I want the user to be able to click a button and it'll run the solver and, if possible, have the user input the Project Raw Cost in a cell and have the solver reference that cell to set the objective function equal to it.

What would be the macro for this? Please help! This will save a lot of time.

Thank you so much!


Excel 2010
ABCD
1
2CategoryDec. Var.% of TotalCost ($)
3Mech HdwX17.7%41,580.92
4Ctrl HdwX27.7%41,579.92
5Mech EngX37.7%41,579.92
6Ctrl EngX47.7%41,579.92
7Comp EngX57.7%41,579.92
8Proj MgmtX67.7%41,579.92
9Mech ResaleX77.7%41,579.92
10Rack ResaleX87.7%41,579.92
11Ctrl ResaleX97.7%41,579.92
12Comp ResaleX107.7%41,579.92
13Mech IntallX117.7%41,579.92
14Elec InstallX127.7%41,579.92
15FreightX137.7%41,579.92
16TotalY1100%
17
18Project Raw Cost540,540.00
Sheet1
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
See if this example helps:

Code:
' this goes at the sheet module
Private Sub CommandButton1_Click()
Dim objc$, vof
objc = "$d$6"                       ' address of objective cell
vof = [b16]                         ' cell containing desired value


SolverOk SetCell:=objc, MaxMinVal:=3, ValueOf:=vof, ByChange:="$B$3:$B$5", _
Engine:=2, EngineDesc:="Simplex LP"
SolverSolve
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

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
Back
Top