Minimization VBA Code

[matthew_ong12]

[TABLE="width: 428"]
<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>[TR]
[TD="class: xl99, width: 183, colspan: 2"]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:

[TABLE="width: 428"]
<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>[TR]
[TD="class: xl99, width: 183, colspan: 2"]Fuel Optimization[/TD]
[TD="class: xl98, width: 81"][/TD]
[TD="class: xl98, width: 74"][/TD]
[TD="class: xl98, width: 90"][/TD]
[/TR]
[TR]
[TD="class: xl300"]Objective[/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[/TR]
[TR]
[TD="class: xl67"]Fuel Cost (PhP)[/TD]
[TD="class: xl35, align: right"] 22.17 [/TD]
[TD="class: xl18"]Mil[/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[/TR]
[TR]
[TD="class: xl67"]Energy Balance[/TD]
[TD="class: xl35, align: right"] - [/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[/TR]
[TR]
[TD="class: xl36"]Fuel Type[/TD]
[TD="class: xl298"]∆H (MJ/kg)[/TD]
[TD="class: xl298"](Tons)[/TD]
[TD="class: xl36"](PhP/Ton)[/TD]
[TD="class: xl298"](MJ)[/TD]
[/TR]
[TR]
[TD="class: xl18"]Coal[/TD]
[TD="class: xl18, align: right"] 23.01 [/TD]
[TD="class: xl35, align: right"] 0.01 [/TD]
[TD="class: xl18, align: right"]4800[/TD]
[TD="class: xl18, align: right"] 0.17 [/TD]
[/TR]
[TR]
[TD="class: xl18"]Napier[/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18, align: right"]0[/TD]
[TD="class: xl18"][/TD]
[TD="class: xl35, align: right"] - [/TD]
[/TR]
[TR]
[TD="class: xl18"]Wood Chips[/TD]
[TD="class: xl35, align: right"] 12.53 [/TD]
[TD="class: xl18, align: right"]7917.186906[/TD]
[TD="class: xl18, align: right"]2800[/TD]
[TD="class: xl35, align: right"] 99,202.35 [/TD]
[/TR]
[TR]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18"][/TD]
[TD="class: xl18, align: right"] 99,202.53 [/TD]
[/TR]
[TR]
[TD="class: xl18"]Bagasse[/TD]
[TD="class: xl35, align: right"] 9.06 [/TD]
[TD="class: xl18, align: right"] 10,950.00 [/TD]
[TD="class: xl18, align: right"]2600[/TD]
[TD="class: xl35, align: right"] 99,202.53 [/TD]
[/TR]
</tbody>[/TABLE]

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[/TD]
[TD="class: xl98, width: 81"][/TD]
[TD="class: xl98, width: 74"][/TD]
[TD="class: xl98, width: 90"][/TD]
[/TR]
</tbody>[/TABLE]

 

[matthew_ong12]

Hi Fazza, I did that one already

 

[Fazza]

great.

which lines causes the error?

 

[matthew_ong12]

Hi Fazza, the first line of the code. Its says that sub or function not defined

 

[Fazza]

I think all I can suggest is you re-visit https://docs.microsoft.com/en-us/off...-vba-functions

 

[matthew_ong12]

Ok but is the code correct?
What should i edit it to fit it as per the vba code?

 

[matthew_ong12]

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

 

[matthew_ong12]

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