excel - VBA code to find value based on seek value

Question

I want to write a piece of VBA CODE that will goal seek the variable value into the static value based on the input value

!

ie. something along these lines but i manually did these,

!

Answer 1

OK, here is what you have to do. First, you set the layout of your worksheet looks like this:

Here I use Sheet1. Please make changes accordingly if necessary.

In order to use Solver add-in, you must first make sure that the add-in is installed. For Office 2013 and later:

1. Click the File tab, and then click Options below the Excel tab.
2. In the Excel Options dialog box, click Add-Ins.
3. In the Manage drop-down box, select Excel Add-ins, and then click Go.
4. In the Add-Ins dialog box, select Solver Add-in, and then click OK.

After that, you must set a reference to the add-in in the workbook containing the code Visual Basic Editor (VBE) that calls the add-in's procedures. Click References on the Tools menu (see pictures below), and then select Solver under Available References.

Now, go to worksheet 1 code module in VBE. Just click Sheet1 twice in VBE and paste the following code into it:

Sub VBASolver()
SolverReset
SolverOk SetCell:="$F$2", MaxMinVal:=3, ValueOf:=0, ByChange:="$B$2:$B$4"
SolverSolve True
End Sub

You can run the loaded VBA program by clicking Run icon below Debug menu or pressing F5 key on the keyboard.