Solvers in macro

[steven031297]

Beste

Mijn excel bestand bestaat uit 4 sheets

Op sheet 1 wil ik een macro aanroepen die solver runt op sheet 2,3 en 4.

Ik kan de solver runnen met een macro die op dezelfde sheet staat, maar vanop een andere sheet lukt dit niet (macro op sheet 2 kan de solver op sheet 2 aanroepen)

Kan iemand me helpen ?

 

[edmoor]

Laat eens zien wat je nu hebt.

 

[steven031297]

Voorlopige code

Sub Test()

End Sub
Sub Macro1()
'
' Macro1 Macro
'

'
SolverOk SetCell:="$I$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$3,$F$3", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$I$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$3,$F$3", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub
Sub Macro2()
'
' Macro2 Macro
'

'
SolverOk SetCell:="$I$4", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$4", Engine:=1 _
, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$I$4", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$4", Engine:=1 _
, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub
Sub Macro3()
'
' Macro3 Macro
'

'
SolverOk SetCell:="$I$3", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$4", Engine:=1 _
, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$I$3", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$4", Engine:=1 _
, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub

 

[edmoor]

Probeer het eens zo:

Sub Test()
    Call Macro1("Sheet2")
    Call Macro2("Sheet2")
    Call Macro3("Sheet2")

    Call Macro1("Sheet3")
    Call Macro2("Sheet3")
    Call Macro3("Sheet3")

    Call Macro1("Sheet4")
    Call Macro2("Sheet4")
    Call Macro3("Sheet4")
End Sub

Sub Macro1(sht As String)
    With Sheets(sht)
        SolverOk SetCell:="$I$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$3,$F$3", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverOk SetCell:="$I$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$3,$F$3", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve
    End With
End Sub

Sub Macro2(sht As String)
    With Sheets(sht)
        SolverOk SetCell:="$I$4", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$4", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverOk SetCell:="$I$4", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$4", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve
    End With
End Sub

Sub Macro3(sht As String)
    With Sheets(sht)
        SolverOk SetCell:="$I$3", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$4", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverOk SetCell:="$I$3", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$4", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve
    End With
End Sub

Even aanpassen uiteraard wat voor jou de volgorde moet zijn.