Looping the Solver function using Macros

[abhishekgupta]

Sub Macro1()
'
' Macro1 Macro
'

'
SolverOk SetCell:="$BD$8", MaxMinVal:=3, ValueOf:=0, ByChange:="$AJ$8", Engine _
:=1, EngineDesc:="GRG Nonlinear"""
SolverOk SetCell:="$BD$8", MaxMinVal:=3, ValueOf:=0, ByChange:="$AJ$8", Engine _
:=1, EngineDesc:="GRG Nonlinear"""
SolverSolve
End Sub

I want to run the above code for cell B8 till B15 (& AJ8 till AJ15 correspondingly), how to run this? Please suggest the appropriate code changes.

 

[Joe4]

Welcome to the Board!

I don't really use Solver much, but it seems that your question is more about loops and ranges than it is Solver.
See if this works for you:

Sub Macro1()
'
' Macro1 Macro

    Dim r As Long
    Dim rng1 As Range
    Dim rng2 As Range
    
'   Loop through rows 8 to 15
    For r = 8 To 15
'       Build range references
        Set rng1 = Range("BD" & r)
        Set rng2 = Range("AJ" & r)
'       Run solver
        SolverOk SetCell:=rng1, MaxMinVal:=3, ValueOf:=0, ByChange:=rng2, Engine _
            :=1, EngineDesc:="GRG Nonlinear"""
        SolverOk SetCell:=rng1, MaxMinVal:=3, ValueOf:=0, ByChange:=rng2, Engine _
            :=1, EngineDesc:="GRG Nonlinear"""
        SolverSolve
    Next r
    
End Sub

 

[abhishekgupta]

Thanks for the reply, but did not work... after running the macros, the AJ & BD row started showing the cell names...

 

[Joe4]

Since you did not include column headers in your image, I have no idea which of those columns and AJ and BD.

And was the original code that you posted actually working for row 8 or not?
I was working off of the assumption that it was.

 

[abhishekgupta]

 

[abhishekgupta]

any recommendations??

 

[BadgerRoad]

I have done this successfully, but I did it a little differently. I put each call to Solver in a separate macro rather than a loop. However, I don't see why a loop wouldn't work. There are some Solver commands that I don't see in your code though.
One is SolverAdd which are your constraints.
Also, my SolverSolve command is SolverSolve userfinish:=True
The last command is Solverreset

I did get my code by recording a macro which as you probably know sometimes generates extra code that may not be necessary. Nevertheless, that's what I did and it works. Hope this is helpful.

 

[Joe4]

Thanks for the reply, but did not work... after running the macros, the AJ & BD row started showing the cell names...

I guess I don't understand quite what the issue is. I don't see anything odd in your last image.
In looking at the last image you posted, can you explain to us exactly what the current issue is?