In the code below I get a runtime error 13 or error 2036 or error 2007. The program uses Solver in excel to find best fit solutions for a matrix of i and j values by minimising error values (Val8a).
the run time error message occurs at 1 of 2 lines (in bold below).
Val8a = Range("t9").Value
Occurs here because cell t9 has an error. The problem seems to be one with solver also as I have constraints on cells t2, t3, t4 and it doesnt always meet those constraints.
When running the corsor over the error line either an icon for error number 2036 or 2007 appears. As I am not as familiar with VB as I should be can i get it to recognise the error and go back to the previous values which didnt give an error.
I believe limatations with solver may be as much to blame.
Anyone got any ideas? I am very new to programming and VB so hopefully haven't missed something obvious.
Dim fQ, Vol1, Vol2, Vol1a, Vol2a, Val1, Val2, Val3, Val4, Val5, Val6, Val7 As Double
Dim NewVal8, Val8a As Double
Dim NewVal1, NewVal2, NewVal3, Val1a, Val2a, Val3a As Double
Maximum = 20
(formatting code left out)
For j = 1 To Maximum
Sheets("Estimation").Select
Range("t5").FormulaR1C1 = j
For i = 1 To j
NewVal8 = 99999
Sheets("Estimation").Select
For fQ = 0.3 To 0.8 Step 0.5
For Vol1 = 3 To 8 Step 5
For Vol2 = 3 To 8 Step 5
Range("t6").FormulaR1C1 = i
Range("t2").FormulaR1C1 = fQ
Range("t3").FormulaR1C1 = Vol1
Range("t4").FormulaR1C1 = Vol2
SolverOk SetCell:="$t$9", MaxMinVal:=2, ValueOf:="0", ByChange:="$t$2:$t$4"
SolverSolve UserFinish:=True
Val1a = Range("t2").Value
Val2a = Range("t3").Value
Val3a = Range("t4").Value
Val8a = Range("t9").Value
If NewVal8 - Val8a > 0.01 And Val1a > 0.01 And Val1a < 0.99 And Val2a > 0.01 And Val3a > 0.01 Then
NewVal1 = Val1a
NewVal2 = Val2a
NewVal3 = Val3a
NewVal8 = Val8a
End If
Next Vol2
Next Vol1
Next fQ
Range("t6").FormulaR1C1 = i
Range("t2").FormulaR1C1 = NewVal1
Range("t3").FormulaR1C1 = NewVal2
Range("t4").FormulaR1C1 = NewVal3
With Worksheets("Estimation")
SolverOk SetCell:="$t$9", MaxMinVal:=2, ValueOf:="0", ByChange:="$t$2:$t$4"
SolverSolve UserFinish:=True
Val1 = Range("t2").Value
Val2 = Range("t3").Value
Val3 = Range("t4").Value
Val4 = Range("t5").Value
Val5 = Range("t6").Value
Val6 = Range("t7").Value
Val7 = Range("t8").Value
Val8 = Range("t9").Value
Range("t2:t9").Copy
Select Case i
Case i
Range("p15").Select
ActiveCell.Offset(rowOffset:=(j - 1) * 9, columnOffset:=i - 1).PasteSpecial _
Paste:=xlPasteValues
(formatting code left out)
Next i
Next j
End Sub