CODEWITHSUNDEEP
vbaexcelsolver
Ted
Ted

Reputation: 27

Solver VBA macro across multiple columns and rows

New to VBA. Scoured the internet, can't come up with solution (but I learned a lot in the process).

I am running solver to solve for a revenue number that will give me a specified profit margin given certain expenses. I've got that all set and a Sub that will perform this task properly for one month. However, I am trying to apply this to 12 columns (12-months). Then step the loop down 19 rows. Run solver across those 12 columns and so on 6-times.

Here is the code:

Sub Monthly()


SolverReset
SolverAdd CellRef:="$d$40", Relation:=2, FormulaText:="$d$41"
SolverOk SetCell:="$d$40", MaxMinVal:=1, ValueOf:=0, ByChange:="$d$24", Engine _
    :=1, EngineDesc:="GRG Nonlinear"
SolverSolve True

SolverReset
SolverAdd CellRef:="$e$40", Relation:=2, FormulaText:="$e$41"
SolverOk SetCell:="$e$40", MaxMinVal:=1, ValueOf:=0, ByChange:="$e$24", Engine _
    :=1, EngineDesc:="GRG Nonlinear"
SolverSolve True

This is an example that would get me January and February. Want it to run through the remaining 10-months (without having to copy and paste this 10-times and manually type in the cell coordinates for F-O columns). Then the code below is for the step down 19 rows:

SolverReset
SolverAdd CellRef:="$d$59", Relation:=2, FormulaText:="$d$60"
SolverOk SetCell:="$d$59", MaxMinVal:=1, ValueOf:=0, ByChange:="$d$43", Engine _
    :=1, EngineDesc:="GRG Nonlinear"
SolverSolve True


End Sub

And again, I would like this to run across 12 columns and step down 19 rows to do it again 4 more times. So the end result is solver running 6 times across 12 columns for a total of 72 times. I would prefer to not type those cell coordinates that many times, and it's probably taxing on my CPU to do it that way. Thanks!

UPDATE:

I am now getting an error, seemingly for no reason. This thing was working great. 

Sub MonthlySolve1a()
    Dim c As Range
    Set c = ActiveSheet.Range("D40")
    MonthlySolve1b c
End Sub

'solve 12 months
Sub MonthlySolve1b(c As Range)
Dim m  As Long
For m = 1 To 12

    SolverReset
    SolverAdd CellRef:=c.Address(), Relation:=2, FormulaText:=c.Offset(1, 0).Address()
    SolverOk SetCell:=c.Address(), MaxMinVal:=1, ValueOf:=0, _
            ByChange:=c.Offset(-16, 0).Address(), Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    Set c = c.Offset(0, 1)
Next m
End Sub

I probably did something stupid in my sheet that messed it up, but any insight would be greatly appreciated. Thanks!

Upvotes: 1

Views: 2411

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

You can use a loop to run solver on each month:

Sub Tester()
    Dim c As Range
    Set c = ActiveSheet.Range("D40")
    Monthly c
End Sub


'solve 12 months
Sub Monthly(c As Range)
    Dim m  As Long
    For m = 1 To 12

        SolverReset
        SolverAdd CellRef:=c.Address(), Relation:=2, FormulaText:=c.Offset(1, 0).Address()
        SolverOk SetCell:=c.Address(), MaxMinVal:=1, ValueOf:=0, _
                ByChange:=c.Offset(-16, 0).Address(), Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve True

        Set c = c.Offset(0, 1)
    Next m
End Sub

Upvotes: 2

Related Questions