Excel Weight Solver Script!

[infraxo]

I would like to make a weight solver, such that:

If I am given three values A B C and a fourth value D, the script will calculate the best approximate weights of A B and C that will give D.

For example, given

A = 10
B = 5
C = 2
and
D = 6.136

I would like the script to calculate approximations such as:

wA = 0.4
wB = 0.31
wC = 0.29

or indeed the exact answer (if it exists):

wA = 0.4
wB = 0.312
wC = 0.288

Please help me! Anything would be appreciated.

 

[infraxo]

Any ideas?

 

[infraxo]

Come on lads

 

[infraxo]

Anyone?

 

[CharlesChuckieCharles]

This code users solver when a particular cell is changed

it also hides the solver dialog box

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, ActiveWorkbook.Names("bmweightEuro").RefersToRange) Is Nothing Then
 
    Range("E6").Select
    solverreset
    SolverOk SetCell:="$E$6", MaxMinVal:=3, ValueOf:="0.3", ByChange:="$D$4:$D$5"
    SolverAdd CellRef:=Range("D6"), Relation:=2, FormulaText:=1
    SolverAdd CellRef:=Range("D4"), Relation:=3, FormulaText:=0.1
    SolverAdd CellRef:=Range("D5"), Relation:=3, FormulaText:=0.1
    SolverSolve userfinish:=True

End If
end sub

Should get you started

 

[infraxo]

Why do I get a:

Compile error:

Sub or Function not defined

?

 

[CharlesChuckieCharles]

Check your References under Tools menu in VBA editor

is the SOLVER entry ticked?

also I'm checking a named Range called "bmweightEuro" which you won't have so you have to adapt it

 

[infraxo]

Check your References under Tools menu in VBA editor

is the SOLVER entry ticked?

also I'm checking a named Range called "bmweightEuro" which you won't have so you have to adapt it

Tools > References is greyed out for me!

 

[CharlesChuckieCharles]

are you currently running the macro in break mode?

 

[infraxo]

I got it now...

But mate this is tiny, can you give me more of a hand please?