Solver iteration in VBA macro


M

Mike Walker

The following VBA code runs Solver for one column (E) in a spreadsheet. I need to run it successivley for a series of columns. The row references do not change but the column references increment through F,G,H etc.

Can you show me how to program this? Thanks in advance.

Sub DAL()
'
' DAL Macro
' Macro recorded 4/18/2011 by local.admin
'


SolverOk SetCell:="$E$25", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$E$10,$E$14,$E$28,$E$29,$E$41"
SolverAdd CellRef:="$E$37", Relation:=2, FormulaText:="0"
SolverOk SetCell:="$E$25", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$E$10,$E$14,$E$28,$E$29,$E$41"
SolverAdd CellRef:="$E$38", Relation:=2, FormulaText:="0"
SolverOk SetCell:="$E$25", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$E$10,$E$14,$E$28,$E$29,$E$41"
SolverAdd CellRef:="$E$47", Relation:=2, FormulaText:="0"
SolverOk SetCell:="$E$48", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$E$10,$E$14,$E$28,$E$29,$E$41"
SolverAdd CellRef:="$E$48", Relation:=2, FormulaText:="0"
SolverOk SetCell:="$E$48", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$E$10,$E$14,$E$28,$E$29,$E$41"
SolverSolve

End Sub
 
Ad

Advertisements

G

GS

One way...

Sub DAL2()
Dim sByChange As String, sCurCol As String
Dim sCRef1 As String, sCRef2 As String
Dim sCRef3 As String, sCRef4 As String
Dim sCSet1 As String, sCSet2 As String, vSz As Variant
Const sCol_List As String = "F,G,H,I,J,K,L,M,N" '//edit to suit

sByChange = "$E$10,$E$14,$E$28,$E$29,$E$41"
sCRef1 = "$E$37": sCRef2 = "$E$38"
sCRef3 = "$E$47": sCRef4 = "$E$48"
sCSet1 = "$E$25": sCSet2 = "$E$48"
sCurCol = "E"

For Each vSz In Split(sCol_List, ",")
SolverOk SetCell:=sCSet1, MaxMinVal:=3, ValueOf:="0", _
ByChange:=sByChange
SolverAdd CellRef:="$E$37", Relation:=2, FormulaText:="0"
SolverOk SetCell:=sCSet1, MaxMinVal:=3, ValueOf:="0", _
ByChange:=sByChange
SolverAdd CellRef:="$E$38", Relation:=2, FormulaText:="0"
SolverOk SetCell:=sCSet1, MaxMinVal:=3, ValueOf:="0", _
ByChange:=sByChange
SolverAdd CellRef:="$E$47", Relation:=2, FormulaText:="0"
SolverOk SetCell:=sCSet2, MaxMinVal:=3, ValueOf:="0", _
ByChange:=sByChange
SolverAdd CellRef:="$E$48", Relation:=2, FormulaText:="0"
SolverOk SetCell:=sCSet2, MaxMinVal:=3, ValueOf:="0", _
ByChange:=sByChange
SolverSolve

'Reset to next column
sByChange = Replace(sByChange, sCurCol, vSz)
sCSet1 = Replace(sCSet1, sCurCol, vSz)
sCSet2 = Replace(sCSet2, sCurCol, vSz)
sCRef1 = Replace(sCRef1, sCurCol, vSz)
sCRef2 = Replace(sCRef2, sCurCol, vSz)
sCRef3 = Replace(sCRef3, sCurCol, vSz)
sCRef4 = Replace(sCRef4, sCurCol, vSz)
sCurCol = vSz
Next
End Sub
 
Ad

Advertisements

G

GS

Oops! -Forgot to insert CellRef vars...

Sub DAL2()
Dim sByChange As String, sCurCol As String
Dim sCRef1 As String, sCRef2 As String, sCRef3 As String, sCRef4 As
String
Dim sCSet1 As String, sCSet2 As String, vSz As Variant
Const sCol_List As String = "F,G,H,I,J,K,L,M,N" '//edit to suit

sByChange = "$E$10,$E$14,$E$28,$E$29,$E$41"
sCRef1 = "$E$37": sCRef2 = "$E$38": sCRef3 = "$E$47": sCRef4 =
"$E$48"
sCSet1 = "$E$25": sCSet2 = "$E$48"
sCurCol = "E"

For Each vSz In Split(sCol_List, ",")
SolverOk SetCell:=sCSet1, MaxMinVal:=3, ValueOf:="0", _
ByChange:=sByChange
SolverAdd CellRef:=sCRef1, Relation:=2, FormulaText:="0"
SolverOk SetCell:=sCSet1, MaxMinVal:=3, ValueOf:="0", _
ByChange:=sByChange
SolverAdd CellRef:=sCRef2, Relation:=2, FormulaText:="0"
SolverOk SetCell:=sCSet1, MaxMinVal:=3, ValueOf:="0", _
ByChange:=sByChange
SolverAdd CellRef:=sCRef3, Relation:=2, FormulaText:="0"
SolverOk SetCell:=sCSet2, MaxMinVal:=3, ValueOf:="0", _
ByChange:=sByChange
SolverAdd CellRef:=sCRef4, Relation:=2, FormulaText:="0"
SolverOk SetCell:=sCSet2, MaxMinVal:=3, ValueOf:="0", _
ByChange:=sByChange
SolverSolve

'Reset to next column
sByChange = Replace(sByChange, sCurCol, vSz)
sCSet1 = Replace(sCSet1, sCurCol, vSz)
sCSet2 = Replace(sCSet2, sCurCol, vSz)
sCRef1 = Replace(sCRef1, sCurCol, vSz)
sCRef2 = Replace(sCRef2, sCurCol, vSz)
sCRef3 = Replace(sCRef3, sCurCol, vSz)
sCRef4 = Replace(sCRef4, sCurCol, vSz)
sCurCol = vSz
Next
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top