Solver iteration in VBA macro
 

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

Solver iteration in VBA macro
 

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Solver iteration in VBA macro
 

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc