Excel VBA Solver Looping Multiple Columns but not updating References

B

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,773
Office Version
  1. 2021
Platform
  1. Windows
Dear All,

The VBA code runs but Solver references doesn't update properly. In Col I Solver only remembers Col Q references plus Range(H92:Q92) doesn't zero out.

<title>Excel Jeanie HTML</title>******>


<!-- ######### Start Created Html Code To Copy ########## -->


RES Var

AEFGHIJKLMNOPQR
2 2018 2019 2019 2019 2019 2019 2019 2019 2019 2019 2019 2019 2019 2020
3 Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul
80New $2,010,356 2,273,836 2,254,752 1,978,988 1,130 2,096,530 981,120 1,329,598 1,676,217 940,391 1,978,790 1,570,310 2,717,934
81New rate Ent ===>0.091671920.10050.1056218690.00010.13620.07430.09370.11830.06990.11680.08690.11780.07910.074989395
82New rate Movt 5.6%5.9%-99.9%143180.3%-42.5%32.7%32.9%-37.8%75.6%-21.7%42.8%-29.2%
83MoM% 5.1%-99.9%136100.0%-45.4%26.1%26.3%-40.9%67.1%-25.6%35.6%-32.9%
84
85New Rate Cal 0.10050.10560.00010.18050.06460.10070.11920.06460.12760.08120.12340.075
86
87
88Total $ 6,627,187 6,503,032 5,650,420 3,378,568 4,547,158 3,831,142 4,122,848 4,120,767 3,926,217 4,948,151 5,250,828 6,764,256
89Total Volume 76,181,424 73,492,588 64,138,566 58,518,891 47,041,022 48,074,184 48,072,808 44,301,919 49,845,329 53,435,936 62,985,744 73,016,698
90Overall Rate 86.9988.4988.1057.7396.6679.6985.7693.0278.7792.6083.3792.64
91Required Rate 86.9988.4986.2685.5885.5186.1086.4186.4986.8086.8987.5288.05
92var 0.000.001.83-27.8411.15-6.41-0.646.52-8.035.71-4.164.59

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 129px;"><col style="width: 122px;"><col style="width: 122px;"><col style="width: 122px;"><col style="width: 122px;"><col style="width: 122px;"><col style="width: 122px;"><col style="width: 122px;"><col style="width: 122px;"><col style="width: 122px;"><col style="width: 122px;"><col style="width: 122px;"><col style="width: 122px;"><col style="width: 122px;"><col style="width: 122px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
E80=E66*(1-E73)
F80=F66*(1-F73)
G80=G66*(1+G73)
H80=H66*(1+H73)
I80=I66*(1+I73)
J80=J66*(1+J73)
K80=K66*(1+K73)
L80=L66*(1+L73)
M80=M66*(1+M73)
N80=N66*(1+N73)
O80=O66*(1+O73)
P80=P66*(1+P73)
Q80=Q66*(1+Q73)
E81=E80/E71
R81=R77*(1-R73)
F82=(F81-F77)/F77
G82=(G81-G77)/G77
H82=(H81-H77)/H77
I82=(I81-I77)/I77
J82=(J81-J77)/J77
K82=(K81-K77)/K77
L82=(L81-L77)/L77
M82=(M81-M77)/M77
N82=(N81-N77)/N77
O82=(O81-O77)/O77
P82=(P81-P77)/P77
Q82=(Q81-Q77)/Q77
G83=G81/F81-1
H83=H81/G81-1
I83=I81/H81-1
J83=J81/I81-1
K83=K81/J81-1
L83=L81/K81-1
M83=M81/L81-1
N83=N81/M81-1
O83=O81/N81-1
P83=P81/O81-1
Q83=Q81/P81-1
F85=ROUND(F81*(1-F93),4)
G85=ROUND(G81*(1-G93),4)
H85=ROUND(H81*(1-H93),4)
I85=ROUND(I81*(1-I93),4)
J85=ROUND(J81*(1-J93),4)
K85=ROUND(K81*(1-K93),4)
L85=ROUND(L81*(1-L93),4)
M85=ROUND(M81*(1-M93),4)
N85=ROUND(N81*(1-N93),4)
O85=ROUND(O81*(1-O93),4)
P85=ROUND(P81*(1-P93),4)
Q85=ROUND(Q81*(1-Q93),4)
F88=SUM(F5:F51)+SUM('SME Var'!F5:F51)
G88=SUM(G5:G51)+SUM('SME Var'!G5:G51)
H88=SUM(H5:H51)+SUM('SME Var'!H5:H51)
I88=SUM(I5:I51)+SUM('SME Var'!I5:I51)
J88=SUM(J5:J51)+SUM('SME Var'!J5:J51)
K88=SUM(K5:K51)+SUM('SME Var'!K5:K51)
L88=SUM(L5:L51)+SUM('SME Var'!L5:L51)
M88=SUM(M5:M51)+SUM('SME Var'!M5:M51)
N88=SUM(N5:N51)+SUM('SME Var'!N5:N51)
O88=SUM(O5:O51)+SUM('SME Var'!O5:O51)
P88=SUM(P5:P51)+SUM('SME Var'!P5:P51)
Q88=SUM(Q5:Q51)+SUM('SME Var'!Q5:Q51)
F89=SUM('RES TPL Vol'!S12:S58)+SUM('SME TPL Vol'!S12:S58)
G89=SUM('RES TPL Vol'!T12:T58)+SUM('SME TPL Vol'!T12:T58)
H89=SUM('RES TPL Vol'!U12:U58)+SUM('SME TPL Vol'!U12:U58)
I89=SUM('RES TPL Vol'!V12:V58)+SUM('SME TPL Vol'!V12:V58)
J89=SUM('RES TPL Vol'!W12:W58)+SUM('SME TPL Vol'!W12:W58)
K89=SUM('RES TPL Vol'!X12:X58)+SUM('SME TPL Vol'!X12:X58)
L89=SUM('RES TPL Vol'!Y12:Y58)+SUM('SME TPL Vol'!Y12:Y58)
M89=SUM('RES TPL Vol'!Z12:Z58)+SUM('SME TPL Vol'!Z12:Z58)
N89=SUM('RES TPL Vol'!AA12:AA58)+SUM('SME TPL Vol'!AA12:AA58)
O89=SUM('RES TPL Vol'!AB12:AB58)+SUM('SME TPL Vol'!AB12:AB58)
P89=SUM('RES TPL Vol'!AC12:AC58)+SUM('SME TPL Vol'!AC12:AC58)
Q89=SUM('RES TPL Vol'!AD12:AD58)+SUM('SME TPL Vol'!AD12:AD58)
F90=F88/F89*1000
G90=G88/G89*1000
H90=H88/H89*1000
I90=I88/I89*1000
J90=J88/J89*1000
K90=K88/K89*1000
L90=L88/L89*1000
M90=M88/M89*1000
N90=N88/N89*1000
O90=O88/O89*1000
P90=P88/P89*1000
Q90=Q88/Q89*1000
F92=F90-F91
G92=G90-G91
H92=H90-H91
I92=I90-I91
J92=J90-J91
K92=K90-K91
L92=L90-L91
M92=M90-M91
N92=N90-N91
O92=O90-O91
P92=P90-P91
Q92=Q90-Q91

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


　

　

<!-- ######### End Created Html Code To Copy ########## -->


　




Code: 
Sub MultipleSolvers()
    Application.ScreenUpdating = False
    Dim i As Long, iStart As Long, iEnd As Long
       
'~~> This prevents an error at blank cells
    On Error Resume Next
    
    
'SolverAdd(CellRef, Relation, FormulaText)
'
'CellRef   Required Variant. A reference to a cell or a range of cells that forms the left side of a constraint.
'Relation   Required Integer. The arithmetic relationship between the left and right sides of the constraint.
'If you choose 4, 5 or 6, CellRef must refer to decision variable cells, and FormulaText should not be specified.
    
    
'Relation    Arithmetic relationship
'1             <=
'2             =
'3             >=
'4             Cells referenced by CellRef must have final values that are integers.
'5             Cells referenced by CellRef must have final values of either 0 (zero) or 1.
'6             Cells referenced by CellRef must have final values that are all different and integers.
 
iStart = Cells(1, "G").Column
iEnd = Cells(1, "Q").Column
     For i = iStart To iEnd
     
     Debug.Print "SetCell: " & Cells(92, i).Address
     Debug.Print "ByChange: " & Cells(81, i).Address
     Debug.Print "Number" & i
     
     SolverReset
     SolverOptions Precision:=0.001
     
      SolverOk SetCell:=Cells(92, i).Address(0, 0), MaxMinVal:=3, ValueOf:=0, ByChange:=Cells(81, i).Address(0, 0), _
          Engine:=1, EngineDesc:="GRG Nonlinear"
          
      SolverAdd cellRef:=Cells(92, i).Address, relation:=3, formulaText:=0.001
    
    
       SolverSolve userFinish:=True
       
   Next i
    
        'Application.ScreenUpdating = True
   Application.Calculation = xlAutomatic
End Sub

Your help would be greatly appreciated.

Kind Regards

Biz
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
You must log in or register to reply here.

Similar threads

C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
H
  • Question
Time value to contain Months/Date/Year Hour:Minutes:Seconds AM/PM
Replies
4
Views
620
hsandeep
H
H
  • Question
Value corresponding to flag cell
Replies
5
Views
834
Fluff
Fluff
H
  • Question
Multiple IF
2
Replies
13
Views
1K
TAPS_MikeDion
TAPS_MikeDion
B
  • Question
Excel VBA Sort Largest to Smallest with Row Groups
2
Replies
14
Views
5K
Biz
B

Forum statistics

Threads
1,215,475
Messages
6,125,028
Members
449,205
Latest member
Eggy66

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top