Fw: [VBA ] 規劃求解
※ [本文轉錄自 Visual_Basic 看板 #1OC1QXPU ]
作者: Tampa (光芒) 看板: Visual_Basic
標題: [VBA ] 規劃求解
時間: Sat Nov 19 17:08:47 2016
各位大大好
我用的是excel 2010
有試著錄製一段規劃求解的巨集
程式碼如下
快速鍵: Ctrl+s
'
SolverOk SetCell:="$AB$2", MaxMinVal:=2, ValueOf:=0, ByChange:="$G$2",
Engine:= _
3, EngineDesc:="Evolutionary"
SolverDelete CellRef:="$G$3432", Relation:=1, FormulaText:="48"
SolverAdd CellRef:="$G$2", Relation:=1, FormulaText:="$E$2"
SolverOk SetCell:="$AB$2", MaxMinVal:=2, ValueOf:=0, ByChange:="$G$2",
Engine:= _
3, EngineDesc:="Evolutionary"
SolverDelete CellRef:="$G$3432", Relation:=4
SolverAdd CellRef:="$G$2", Relation:=4, FormulaText:="整數"
SolverOk SetCell:="$AB$2", MaxMinVal:=2, ValueOf:=0, ByChange:="$G$2",
Engine:= _
3, EngineDesc:="Evolutionary"
SolverDelete CellRef:="$G$3432", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$E$2", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$AB$2", MaxMinVal:=2, ValueOf:=0, ByChange:="$G$2",
Engine:= _
3, EngineDesc:="Evolutionary"
SolverOk SetCell:="$AB$2", MaxMinVal:=2, ValueOf:=0, ByChange:="$G$2",
Engine:= _
3, EngineDesc:="Evolutionary"
SolverSolve
End Sub
簡單來說
我輸入變數在G欄位的所有儲存格,範圍從G2到G17860,限制式如下
G2<=E2
G2>=0
G2=整數
求AB2的最小值
.
.
.
G3<=E3
G3>=0
G3=整數
求AB3的最小值
.
.一直到
.
G17860<=E17860
G17860>=0
G17860=整數
求AB17860的最小值
巨集結束
問題一:規劃求解似乎不能直接用複製來做
所以需要用巨集,但小弟我 對程式碼不很了解
所以附上程式碼,希望各位大大可以指點一下,需要修改哪幾個地方
以方便我順利求解
ps計算方式為演化
感謝
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 36.225.188.16
※ 文章網址: https://www.ptt.cc/bbs/Visual_Basic/M.1479546529.A.65E.html
※ 發信站: 批踢踢實業坊(ptt.cc)
※ 轉錄者: Tampa (36.225.188.16), 11/19/2016 17:10:17
→
11/19 17:56, , 1F
11/19 17:56, 1F
→
11/19 17:57, , 2F
11/19 17:57, 2F
→
11/19 17:58, , 3F
11/19 17:58, 3F
→
11/19 18:03, , 4F
11/19 18:03, 4F
→
11/19 18:03, , 5F
11/19 18:03, 5F
→
11/19 18:13, , 6F
11/19 18:13, 6F
感謝S大
修正程式碼如下
' 快速鍵: Ctrl+s
'
Dim j As Integer
For j = 2 To 17860
SolverOk SetCell:=Range(Cells(j, 28)), MaxMinVal:=2, ValueOf:=0,
ByChange:=Range(Cells(j, 7)), Engine:= _
3, EngineDesc:="Evolutionary"
SolverAdd CellRef:=Range(Cells(j, 7)), Relation:=1,
FormulaText:=Range(Cells(j, 5))
SolverOk SetCell:=Range(Cells(j, 28)), MaxMinVal:=2, ValueOf:=0,
ByChange:=Range(Cells(j, 7)), Engine:= _
3, EngineDesc:="Evolutionary"
SolverAdd CellRef:=Range(Cells(j, 7)), Relation:=4, FormulaText:="整數"
SolverOk SetCell:=Range(Cells(j, 28)), MaxMinVal:=2, ValueOf:=0,
ByChange:=Range(Cells(j, 7)), Engine:= _
3, EngineDesc:="Evolutionary"
SolverAdd CellRef:=Range(Cells(j, 5)), Relation:=3, FormulaText:="0"
SolverOk SetCell:=Range(Cells(j, 28)), MaxMinVal:=2, ValueOf:=0,
ByChange:=Range(Cells(j, 7)), Engine:= _
3, EngineDesc:="Evolutionary"
SolverOk SetCell:=Range(Cells(j, 28)), MaxMinVal:=2, ValueOf:=0,
ByChange:=Range(Cells(j, 7)), Engine:= _
3, EngineDesc:="Evolutionary"
SolverSolve True
Next j
End Sub
逐步執行出現 執行錯誤"1004"
是哪邊沒有改到嗎??
以下附上原檔
https://www.dropbox.com/s/0j1z6za74twmbvp/%E6%B8%AC%E8%A9%A6%E7%94%A8.xlsm?dl=0
※ 編輯: Tampa (36.225.188.16), 11/19/2016 22:06:29
→
11/19 22:16, , 7F
11/19 22:16, 7F
※ 編輯: Tampa (36.225.188.16), 11/19/2016 22:21:36
→
11/19 22:23, , 8F
11/19 22:23, 8F
→
11/19 22:33, , 9F
11/19 22:33, 9F
![](https://i.imgur.com/iSYr83D.jpg)
→
11/19 23:07, , 10F
11/19 23:07, 10F
→
11/19 23:39, , 11F
11/19 23:39, 11F
→
11/19 23:40, , 12F
11/19 23:40, 12F
→
11/20 00:36, , 13F
11/20 00:36, 13F
→
11/20 00:41, , 14F
11/20 00:41, 14F
→
11/20 00:42, , 15F
11/20 00:42, 15F
→
11/20 01:32, , 16F
11/20 01:32, 16F
→
11/20 01:32, , 17F
11/20 01:32, 17F
→
11/20 01:33, , 18F
11/20 01:33, 18F
→
11/20 01:40, , 19F
11/20 01:40, 19F
→
11/20 01:41, , 20F
11/20 01:41, 20F
→
11/20 01:45, , 21F
11/20 01:45, 21F
→
11/20 01:45, , 22F
11/20 01:45, 22F
→
11/20 01:51, , 23F
11/20 01:51, 23F
→
11/20 02:24, , 24F
11/20 02:24, 24F
→
11/20 02:32, , 25F
11/20 02:32, 25F
→
11/20 18:33, , 26F
11/20 18:33, 26F
→
11/20 18:34, , 27F
11/20 18:34, 27F
→
11/20 19:09, , 28F
11/20 19:09, 28F
→
11/20 19:19, , 29F
11/20 19:19, 29F
→
11/20 19:22, , 30F
11/20 19:22, 30F