Minimization VBA Code

matthew_ong12

New Member
Joined
Aug 15, 2018
Messages
18
Good morning Mr Excel, would like to ask how to code the minimization VBA code.

The objective is to minimize the
Fuel cost=Cost of Fuel 1 (Fuel 1 Qty) + Cost of fuel 2 (Fuel 2 Qty)+....
while satisfying the energy balance=Heating Value of Fuel 1 (Fuel 1 Qty)+ Heating Value of Fuel 2 (Fuel 2 Qty)+...

Here's the figures:

Fuel Optimization
Objective
Fuel Cost (PhP) 22.17 Mil
Energy Balance -
Fuel Type∆H (MJ/kg)(Tons)(PhP/Ton)(MJ)
Coal 23.01 0.01 4800 0.17
Napier0 -
Wood Chips 12.53 7917.1869062800 99,202.35
99,202.53
Bagasse 9.06 10,950.00 2600 99,202.53

<colgroup><col width="101" style="width:76pt"> <col width="82" style="width:62pt"> <col width="81" style="width:61pt"> <col width="74" style="width:56pt"> <col width="90" style="width:68pt"> </colgroup><tbody>
</tbody>

The 99,202MJ is the energy balance target that needs to be attain from using other fuel such as coal, napier and wood chips. This is equated to the product sum of Heating Values of other fuel (i.e. coal, napier & wood chips) and their quantity.

Hoping to hear from you soon :)


Regards,
Matthew

<colgroup><col width="101" style="width:76pt"><col width="82" style="width:62pt"><col width="81" style="width:61pt"><col width="74" style="width:56pt"><col width="90" style="width:68pt"></colgroup><tbody>
</tbody>
 

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.
Hi Fazza, I already did the add on on excel but i still encounter the same problem.
Here's the code that was generated when i did the record macro.

Sub FuelOptimization()
'
' FuelOptimization Macro
'


'
SolverOk SetCell:="$B$379", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$382:$C$384" _
, Engine:=1, EngineDesc:="GRG Nonlinear"
SolverDelete CellRef:="$B$380", Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$B$380", Relation:=2, FormulaText:="0"
SolverOk SetCell:="$B$379", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$382:$C$384" _
, Engine:=1, EngineDesc:="GRG Nonlinear"
SolverDelete CellRef:="$C$382", Relation:=1, FormulaText:="$F$382"
SolverAdd CellRef:="$C$382", Relation:=1, FormulaText:="$F$382"
SolverOk SetCell:="$B$379", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$382:$C$384" _
, Engine:=1, EngineDesc:="GRG Nonlinear"
SolverDelete CellRef:="$C$383", Relation:=1, FormulaText:="$F$383"
SolverAdd CellRef:="$C$383", Relation:=1, FormulaText:="$F$383"
SolverOk SetCell:="$B$379", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$382:$C$384" _
, Engine:=1, EngineDesc:="GRG Nonlinear"
SolverDelete CellRef:="$C$384", Relation:=1, FormulaText:="$F$384"
SolverAdd CellRef:="$C$384", Relation:=1, FormulaText:="$F$384"
SolverOk SetCell:="$B$379", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$382:$C$384" _
, Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$B$379", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$382:$C$384" _
, Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub

When i try to run the macro, it displays an error of "sub or function not defined". I'm not quite sure where the error lies since i just use the record macro. Kindly enlighten me
 
Upvote 0
Hi Mr Excel, would like to ask how to change a single reference cell to a range for a goal seek scenario. Here's the code.

Sheets("Fuel Plan").Select
Range("h6").Select
Range("h6").GoalSeek Goal:=Range("i6"), ChangingCell:=Range("E2")
Range("E2").Select

In this example, it only changes one particular value of the cell which is "E2" now if I want to expand it and change it to say "e2:e3" how should I edit the code?

Hoping to hear from you :)
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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