How to increment cell references in solver macro loop

[keros]

I have a worksheet the calculates pricing based on a set margin and npv criteria. Everything I'm referring to is in one sheet. Price input table is in a range of j3:k19. The table reflects pricing by term and product options. Term years 1-7 across columns and product A-X across rows. Tied to that pricing is a financial calculator and two associated data tables. One table for margin outcomes and one for npv outcomes. The range sizes on the price input and data tables are the same. The ordering of the pricing and criteria tables is the same. Objective is to create a solver macro that will solve for the pricing in j3:k19 subject to constraints in the margin and npv data tables. Here is what the solver code looks like for computing the price in cell j3:
SolverReset
SolverOk SetCell:="$K$24", MaxMinVal:=3, ValueOf:=0.2, ByChange:="$J$3", Engine _
:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$B$24", Relation:=3, FormulaText:="0.24"
SolverSolve UserFinish:=True

I want code that will run that same solver code criteria for each cell in the price input range without having to write the same code for every cell with updated cell references in the input and criteria ranges. I know how to set up easy loop macros, but in this example, I don't know how to move the cell references in tandem across the price input and margin and npv criteria tables. So my questions are what is the best way to wrap a loop around this and have the solver calc references move in tandem with the price range reference. Something like this:

Sub Pricing ()
Dim cell As Range
For Each cell In ActiveSheet.Range("j3:k19")
Enter dynamic solver code here
Next cell
End Sub

Appreciate any input I can get. Thanks

[Alf]

I'm not sure I understand you description of your problem. You wish to run solver on each cell in the range J3:K19 but in your uploaded example the cell to change is J3 and the target cell is K24.

Assuming that cells to change is found in the J column and target cell should be in the K column then something like this could be used for the loop:

Please Login or Register  to view this content.

and if the range for cells to change are J:K you can define this as

Please Login or Register  to view this content.

Alf

[keros]

Thanks for helping Alf. I apologize for making a mistake on my range reference. The pricing input table range is actually J3:P19. The IRR table range is K24:Q41 and the margin table range is B24:H41 My VBA skill set is not good, so I may not fully understand how your proposed solution will work. I will play with it tomorrow. I think the the key missing piece here might be having the code increment all of the solver criteria in tandem with the pricing table input referenced cell. Maybe an example image of what I have will help. The actual tables look very similar to this example. So if we pretend the first price input cell of the example pricing input table is cell J3, I am wanting a macro that will solve for the price in J3 that results in an irr of 20% in the irr table or possibly higher, in the event the >= 24% margin constraint in the solver is tripped. I then want the macro to move to cell K3 and do the same thing, but in addition to the move to K3, the solver cell references for irr and margin also have to move/increment to make sure it is referencing the irr and margin associated with the price refernce. Once it gets to cell P3 on the first row of the range, drop to next row and on and on until the end of the range. The actual order of cell movement does not matter as long as every cell in the price input table gets solved. Does this description make more sense? Example.png

[Alf]

Can't see how to set up solver as the correlation between solver an the different tables are not clear to me, can you upload a file shoving what walues shall be changed and how
the result should be?

I've uploaded a small solver model that runs in a loop. There are also some comments in the file to show how the loop is set up. Perhaps this will be of help
to you.

Alf

[keros]

Thanks Alf. I will take a look at this today and see if I can incorporate it into what I'm trying to accomplish. I've attempted to include an attachment this time just in case it gives
you further solution inspiration. Hopefully it helps better describe the objective.

[keros]

I tried using your example exercise. In order to use it, I think I will need to align all my tables into the same column or I end up with code that looks like this trying to mimic your example:
Sub Pricing()
'
' Pricing Macro
'
'
Dim p As Integer
Dim i As Integer
Dim d As Integer
For p = 10 To 16 'for price table increment reference
For i = 11 To 17 'for irr table increment reference
For d = 2 To 8 'for dm table increment reference

SolverReset
SolverOk SetCell:="(24,i)", MaxMinVal:=3, ValueOf:=0.2, ByChange:="(3,p):", Engine _
:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="(24,d)", Relation:=3, FormulaText:="0.24"
SolverSolve UserFinish:=True

' I don 't know how to NEXT when I have multiple "for" statements.

End Sub

[Alf]

I don 't know how to NEXT when I have multiple "for" statements.

Since you have 3 "For" statement you need 3 "Next" and the way it will work is that the last "For" will be "solved" first.

So first p = 10, i = 11 and d = 2 and with this setup solver will do the first run, the second run will use the same p and i value but d will now be 3. For the 3 run d = 4 and p and i are still 10 and 11 and so forth until d = 8.

The next run after d = 8 the i value will change to 12 and the d value will start at 2 again loop through the d values 2 to 8, then change the i value to 13 and loop through all d values and so fort.

When the i = 17 the next loop change the p value to 11.

Please Login or Register  to view this content.

Solver will not understand SetCell:="(24,i)" so I've changed your code a bit. When writing code it should be wrapped click on the "#" icon and write your code between two [CODE] symbols you get.

In you code you wrote Engine:=1, EngineDesc:="GRG Nonlinear" this is not wrong but you only need to specify either "Engine:=1" or EngineDesc:="GRG Nonlinear"

Alf

[keros]

Thanks for your help Alf.

[Alf]

You are welcome and hopefully you now have sufficient information for the solver setup you need. If not don't hesitate to post again

Alf