Increment By Range & Output Solver Result

ibrahim ahmad · April 30th, 2008, 10:16 PM

Please see this link for the file. http://www.singapore-ww2-militaria.com/macroV1.xls

I tried it but it does not work. It is in Module1 under beta1. Probably some problems with the definitions. But the framework is probably there. This model is normally linked to bigger model. I just simplified it to keep it in one model. Therefore the results could look strange. This is no problem. If this works i can adjust it later to the other models.

The function should work as follow:

cell F13 should be J27-(J28+J29) in the first run
afterwards solver with F12 as target cell, N5:N9 as changing cells and the constrains like written.

After the equation is solved cell G44:G58 should be copied in J44:J58 formatted as number
Afterwards Cell J27:J39 should be copied in J60:J72

This was the first run.

In the second run F13 changes to K27-(K28+K29)
solver does not change
G44:G58 should be copied in K44:K58
Afterwards Cell K27:K39 should be copied in K60:K72

In the third run F13 changes to L27-(L28+L29)
solver does not change
G44:G58 should be copied in L44:L58
Afterwards Cell L27:L39 should be copied in L60:L72

Thank You for taking the time to read this & all the help given

Cheers,
Ibrahim,
Singapore

-----------------

Code:

Sub beta1() 

    Dim n As Integer, i As Integer, 
    Dim solver() As Double, 

    n = 20 

    For i = 1 To n 


        Range("F13").Select 
        ActiveCell.FormulaR1C1 = "=R[14]C[4+i]-(R[15]C[4+i]+R[16]C[4+i])" 



        SolverOk SetCell:="$F$12", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$5:$N$9" 
        SolverAdd CellRef:="$F$13", Relation:=2, FormulaText:="0" 
        SolverAdd CellRef:="$N$5", Relation:=3, FormulaText:="0""" 
        SolverAdd CellRef:="$N$6", Relation:=3, FormulaText:="0""" 
        SolverAdd CellRef:="$N$7", Relation:=3, FormulaText:="0""" 
        SolverAdd CellRef:="$N$8", Relation:=3, FormulaText:="0""" 
        SolverAdd CellRef:="$N$9", Relation:=3, FormulaText:="0""" 
        SolverOk SetCell:="$F$12", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$5:$N$9" 
        SolverSolve 

        Range("G44:G58").Select 
        Selection.Copy 

        Range("G44").Select 
        ActiveCell.Select = "R[3+i]C[0]" 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 

        Range("J27:J39").Select 
        Selection.Copy 

        Range("J60").Select 
        ActiveCell.Select = "R[3+i]C[0]" 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 


    Next i 

End Sub