Programmation Excel Solveur VBA
Bonjour, dans le programme si dessus je n'arrive pas à insérer les références des cellules obtenues après les boucles IF dans le TABLEAU A( ) pour créer une plage dynamique des cellules à laquelle je ferai appel quand je vais appeler le solveur VBA d'excel au niveau de ByChange. Si quelqu'un peut m"aider
Option Explicit
Dim TABLEAUA() As variant
Dim TABLEAUB() As variant
Sub PROGRAMMATION()
Dim VARIABLE As Range
'Dim TABLEAUB() As Range
'Dim TABLEAUA() As Variant
Dim ReserveT As Long
Dim ReserveE As Long
Dim ReserveC As Long
Dim j As Long
Dim quota As Long
For j = 1 To 49
ReserveT = Sheets("feuil2").Cells(j + 3, 3)
ReserveE = Sheets("feuil2").Cells(j + 3, 4)
ReserveC = Sheets("feuil2").Cells(j + 3, 5)
quota =Sheets("feuil1").Cells(j + 13, 6)
ReDim Preserve TABLEAUA(j)
ReDim Preserve TABLEAUB(j)
If (quota < ReserveT) And ((quota < ReserveE) And (quota < ReserveC)) Then
Sheets("Feuil3").Activate
TABLEAUA(j) = Cells(8, 1 + j)
TABLEAUA(j) = Cells(9, 1 + j)
TABLEAUA(j) = Cells(10, 1 + j)
'Cells(8, 1 + j) = TABLEAUA(j)
'Cells(9, 1 + j) = TABLEAUA(j)
' Cells(10, 1 + j) = TABLEAUA(j)
'VARIABLE.Select
' VARIABLE = Union(Cells(8, 1 + j), Cells(9, 1 + j), Cells(10, 1 + j))
ElseIf (quota < ReserveT) And ((quota < ReserveE) And (quota >= ReserveC)) Then
Cells(8, 1 + j) = TABLEAUA(j)
Cells(9, 1 + j) = TABLEAUA(j)
Cells(10, 1 + j) = TABLEAUB(j)
' VARIABLE = Union(Cells(8, 1 + j), Cells(9, 1 + j))
ElseIf (quota < ReserveT) And ((quota >= ReserveE) And (quota >= ReserveC)) Then
Cells(8, 1 + j) = TABLEAUA(j)
Cells(9, 1 + j) = TABLEAUB(j)
Cells(10, 1 + j) = TABLEAUB(j)
'VARIABLE = (Cells(8, 1 + j))
ElseIf (quota >= ReserveT) And ((quota >= ReserveE) And (quota >= ReserveC)) Then
Cells(8, 1 + j) = TABLEAUB(j)
Cells(9, 1 + j) = TABLEAUB(j)
Cells(10, 1 + j) = TABLEAUB(j)
End If
Next
'TABLEAUB(j) = 0
End Sub
Sub PROGRAMMATIONLINEAIRE()
'
' ESSAI Macro
'
'
SolverOk SetCell:="$BB$6", MaxMinVal:=2, ValueOf:=0, ByChange:= _
"$BB$6,TABLEAUA(j)", Engine:=2, EngineDesc:="Simplex LP"
SolverAdd CellRef:="$AZ$8:$AZ$10", Relation:=1, FormulaText:="$BA$8:$BA$10"
SolverAdd CellRef:="$AZ$8:$AZ$10", Relation:=1, FormulaText:="$BB$6"
SolverAdd CellRef:="$B$12:$AX$12", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$B$8:$AX$10", Relation:=5, FormulaText:="binaire"
SolverAdd CellRef:="$BB$6", Relation:=3, FormulaText:="$BD$6"
SolverOk SetCell:="$BB$6", MaxMinVal:=2, ValueOf:=0, ByChange:= _
"$BB$6,$B$8:$AX$10", Engine:=2, EngineDesc:="Simplex LP"
SolverOk SetCell:="$BB$6", MaxMinVal:=2, ValueOf:=0, ByChange:= _
"$BB$6,$B$8:$AX$10", Engine:=2, EngineDesc:="Simplex LP"
SolverSolve
End Sub
Sub solver()
Call PROGRAMMATION
Call PROGRAMMATIONLINEAIRE
End Sub