Minimization VBA Code

[matthew_ong12]

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 Napier 0 - Wood Chips 12.53 7917.186906 2800 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>

 

[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