Solver VBA - Result not showing on screen

[JPLouw]

Hi,

I have written a Solver VBA interface in Excel 2010 to optimize the weightings for different models. The idea is to let Solver do the optimization and provide the output weightings, and then move down to the next row of inputs. The code compiles, but it does not display it on screen and in Excel itself it displays "Setting Up Problem..." the whole time in the bottom left corner. Can you please help me with this problem.

Thanks in advance,
Jan

Sub SolverTest1()
'
' SolverTest1 Macro - Recorded by Jan Paul Louw 15/08/2011
'
'
Dim TargetArray As Variant
Dim WeightingOneArray As Variant
Dim WeightingTwoArray As Variant
Dim WeightingThreeArray As Variant
Dim ConstraintArray As Variant
Dim i As Long
 
TargetArray = ThisWorkbook.Worksheets("Data Multiples").Range("M2:M12").Value
WeightingOneArray = ThisWorkbook.Worksheets("Data Multiples").Range("I2:I12").Value
WeightingTwoArray = ThisWorkbook.Worksheets("Data Multiples").Range("J2:J12").Value
WeightingThreeArray = ThisWorkbook.Worksheets("Data Multiples").Range("K2:K12").Value
ConstraintArray = ThisWorkbook.Worksheets("Data Multiples").Range("L2:L12").Value
 
For i = LBound(TargetArray, 1) To UBound(TargetArray, 1)
 
    SolverReset
    SolverAdd CellRef:="ConstraintArray(i, 1)", Relation:=2, FormulaText:="1"
    SolverOk SetCell:="TargetArray(i, 1)", MaxMinVal:=2, ValueOf:="0", _
       ByChange:="WeightingOneArray(i, 1),WeightingTwoArray(i, 1),WeightingThreeArray(i, 1)", _
       Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True
 
Next i
 
End Sub

 

[bensonsearch]

the variables arnt declared as an array for one. just as a variant.

needs to be like

Dim TargetArray(100) As Variant

where the 100 is the number of spots it can hold. fill out by
targetarray(1) = 1
targetarray(2) = something else

etc