Loop Solver through columns

[Kerryx]

Ok first time trying this with solver and putting my hands up my VBA is crap but attempt here to

Sub Solve()
'
' Solve Macro
'
Dim rng As Range
For i = 4 To 8
Columns(i).Select

SolverReset
    SolverOk SetCell:="$" & i & "$13", MaxMinVal:=3, ValueOf:="39.00", ByChange:="$" & i & "$2:""$" & i & "$7" _
        , Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$" & i & "$3", Relation:=1, FormulaText:="6"
    SolverAdd CellRef:="$" & i & "$3", Relation:=3, FormulaText:="2"
    SolverAdd CellRef:="$" & i & "$4", Relation:=3, FormulaText:="0.5"
    SolverAdd CellRef:="$" & i & "$4", Relation:=1, FormulaText:="2"
    SolverAdd CellRef:="$" & i & "$6", Relation:=1, FormulaText:="35"
    SolverAdd CellRef:="$" & i & "$6", Relation:=3, FormulaText:="15"
    SolverAdd CellRef:="$" & i & "$7", Relation:=3, FormulaText:="2"
    SolverAdd CellRef:="$" & i & "$7", Relation:=3, FormulaText:="2"
   
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
    Next i
    
End Sub

Trying to get total to 39 by adjusting values in c3 to c7 with variables as seen in code below and so on across the columns, seems to jump across columns ok but see no change in data, what am i doing wrong?
Thanks for the helps folks.

Project hrs.xlsm
	B	C	D	E	F
1	Jobs	Week1	Week2	Week3	Week4
2	a	2.5	1	1.25	3
3	b	5.5	5	4	7
4	c	1.5	2	2	1
5	d	3.833333
6	e	19.83333	17	29	21
7	f	5.833333	2	3	7
8	g
9	h
10	i
11	j
12	k
13	Total	39	26	38	36
Sheet1

Cell Formulas
Range	Formula
C13:F13	C13	=SUM(C2:C12)

Named Ranges
Name	Refers To	Cells
solver_lhs1	=Sheet1!$C$3	C13
solver_lhs10	=Sheet1!$C$3	C13
solver_lhs11	=Sheet1!$C$4	C13
solver_lhs12	=Sheet1!$C$4	C13
solver_lhs13	=Sheet1!$C$6	C13
solver_lhs14	=Sheet1!$C$6	C13
solver_lhs15	=Sheet1!$C$7	C13
solver_lhs16	=Sheet1!$C$7	C13
solver_lhs17	=Sheet1!$C$3	C13
solver_lhs18	=Sheet1!$C$3	C13
solver_lhs19	=Sheet1!$C$4	C13
solver_lhs2	=Sheet1!$C$3	C13
solver_lhs20	=Sheet1!$C$4	C13
solver_lhs21	=Sheet1!$C$6	C13
solver_lhs22	=Sheet1!$C$6	C13
solver_lhs23	=Sheet1!$C$7	C13
solver_lhs24	=Sheet1!$C$7	C13
solver_lhs25	=Sheet1!$C$3	C13
solver_lhs26	=Sheet1!$C$3	C13
solver_lhs27	=Sheet1!$C$4	C13
solver_lhs28	=Sheet1!$C$4	C13
solver_lhs29	=Sheet1!$C$6	C13
solver_lhs3	=Sheet1!$C$4	C13
solver_lhs30	=Sheet1!$C$6	C13
solver_lhs31	=Sheet1!$C$7	C13
solver_lhs32	=Sheet1!$C$7	C13
solver_lhs33	=Sheet1!$C$3	C13
solver_lhs34	=Sheet1!$C$3	C13
solver_lhs35	=Sheet1!$C$4	C13
solver_lhs36	=Sheet1!$C$4	C13
solver_lhs37	=Sheet1!$C$6	C13
solver_lhs38	=Sheet1!$C$6	C13
solver_lhs39	=Sheet1!$C$7	C13
solver_lhs4	=Sheet1!$C$4	C13
solver_lhs40	=Sheet1!$C$7	C13
solver_lhs5	=Sheet1!$C$6	C13
solver_lhs6	=Sheet1!$C$6	C13
solver_lhs7	=Sheet1!$C$7	C13
solver_lhs8	=Sheet1!$C$7	C13
solver_lhs9	=Sheet1!$C$3	C13

 

[Kaper]

You are trying to change columns, but your variable, i, is an integer type (as you haven't declared it and you assign it an integer vallue)
So your code uses something like $4$13 for a reference (address), while you need something like $D$13 and so on

To change number into character one coul use chr function.
chr(65) = A, chr(66) = B, and so on.

(other option could be cells(i,13).address to get (provided i = 4) $D$13)


So try:

For i = 3 To 7  ' you want to start from column C, right? it is 3rd column
' this is unneded Columns(i).Select
colname = chr(64+i)
SolverReset
    SolverOk SetCell:="$" & colname & "$13", MaxMinVal:=3, ValueOf:="39.00", ByChange:="$" & colname & "$2:""$" & colname & "$7" _
        , Engine:=1, EngineDesc:="GRG Nonlinear"
' etc.

Make sure you have a reference to Solver in VBA Editor (Alt+F11 Alt+T R and make sure checkbox next to Solver is checked)

 

[Kerryx]

Duh should've spotted that big difference between calling numbers and letters
OK updated the code to the below , not throwing up any errors but does not seem to do anything, also have the reference ticked

Sub Solve()
'
' Solve Macro
'
For i = 3 To 7  ' you want to start from column C, right? it is 3rd column
' this is unneded Columns(i).Select

colname = Chr(64 + i)

SolverReset
    SolverOk SetCell:="$" & colname & "$13", MaxMinVal:=3, ValueOf:="39.00", ByChange:="$" & colname & "$3:""$" & colname & "$7" _
        , Engine:=1, EngineDesc:="GRG Nonlinear"

    SolverAdd CellRef:="$" & colname & "$3", Relation:=1, FormulaText:="6"
    SolverAdd CellRef:="$" & colname & "$3", Relation:=3, FormulaText:="2"
    SolverAdd CellRef:="$" & colname & "$4", Relation:=3, FormulaText:="0.5"
    SolverAdd CellRef:="$" & colname & "$4", Relation:=1, FormulaText:="2"
    SolverAdd CellRef:="$" & colname & "$6", Relation:=1, FormulaText:="35"
    SolverAdd CellRef:="$" & colname & "$6", Relation:=3, FormulaText:="15"
    SolverAdd CellRef:="$" & colname & "$7", Relation:=3, FormulaText:="2"
    SolverAdd CellRef:="$" & colname & "$7", Relation:=3, FormulaText:="2"
   
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
    Next i
       
End Sub

 

[Kerryx]

Bump

 

[Kaper]

Two corections in SolverOk:

number 39 in ValueOf
Not text.
if needed with decimal then 39.15 and so on, but not text "39.00"

ValueOf:=39

and correct range (no extra double quotations):

ByChange:="$" & colname & "$3:$" & colname & "$7"

But as a matter of fact you can write all addresses as relative, so:

    SolverOk SetCell:=colname & "13", MaxMinVal:=3, ValueOf:=39, ByChange:=colname & "3:" & colname & "7", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:=colname & "3", Relation:=1, FormulaText:="6"

and so on. Note that FormulaText remains text - in doublequotation marks.

You may also add just before End Sub two lines to ensure that after Solver finishes the excel takes "full control". I noticed that sometimes after executing solver in a loop severel times, worksheet does not show final result until you take some actions (like recalculate). But as I said - you may add it, as well as you may use relative addressing, but above mentioned changes - correcting the range and using number, not text in ValueOf are compulsory.

Application.Calculate
Application.ScreenUpdating = True

 

[Kerryx]

Kaper, thank you very much , added the last 2 lines as you suggested to just before the loop continues i.e. before "Next i" , if i left them after it, just shows result for last column rather than all columns individually, so bang on for that, a great help.

 

[Kaper]

Glad to hear that it worked. And thanks for the important comment on recalculation and screen updating location.