How to trick a Dynamic Name to be Static when SOLVER is ON...

[rml]

From my previous post today "DYNAMIC NAME", I had just been fed by Gary's
Student - single handedly- how to define a dynamic name by VBE....I need to
go to the next level....
The Dynamic Name must be static when I go to the SOLVER
function....Considering that the NAME contains the specific cell refs.
subject to SOLVER's fill in the blanks?

I hope Gary's Student can read this thread...to further continue the
multitask involve on the spreadsheet I am doing.

thanks for any reply....

 

[Gary''s Student]

I am here. Be more specific on what you are trying to achieve.

 

[rml]

In tools menu, we can find the Solver function, when checking it as an
Addin....When the Solver popup form appears, I need to place GROUP_1 as the
changing cells....and on the Constraint...i will put the result
conditions...When i hit the button Solve....I do not know if there will be
conflict with the dyna setup of the autochange of cell refs since Solver has
to fill-in values there (as its function) hence the Group_1 blank cell
refs...may be affected or be triggered to re-update itsel while solver is
still on the process of its iterated solutions....
In the end, I need the dynamic name GROUP_1 to have the cell refs at static
until such time that the SOLVER finds an optimum solution....
I have belief on you and think you can help me for a great deal...

 

[rml]

Gary,
The spreadsheet that contains these 3 GROUP name will act like my template
to for different solver scenarios....The quantitiy of blank cells in GROUP_1
differs on every scenario...these blank cells are linked to other formulas
which will be included as a constraint as well....its complexities differs on
each scenario....these blank cells will act as my container of solver
results...it is not only for any matrix or summing or counting solutions. One
blank cell may act under different units of measurement. (i.e. Currency,
quantity, amount, and a lot more...) You can help me here along with the
solver function under a very small spreadsheet.
thanks for not hanging up...

 

[rml]

I hope you are still around...Please give me feedback if this thread has to
stop and enable me post another thread forward...
sincerely,thanks again

 

[Gary''s Student]

I finally got it.


We are sharing the range GROUP1 with Solver. When WE are changing cell
values in the range, we want the dynamic re-ranger to adjust the names. When
we run Solver, we want the ranges frozen.

Add the following routines to the module (NOT worksheet code area):

Sub freeze()
Application.EnableEvents = False
End Sub

Sub thaw()
Application.EnableEvents = True
End Sub



1. before running Solver, always run freeze
2. after running Solver, run thaw

 

[rml]

Thanks...I'll return back after the test run....

I finally got it.


We are sharing the range GROUP1 with Solver. When WE are changing cell
values in the range, we want the dynamic re-ranger to adjust the names. When
we run Solver, we want the ranges frozen.

Add the following routines to the module (NOT worksheet code area):

Sub freeze()
Application.EnableEvents = False
End Sub

Sub thaw()
Application.EnableEvents = True
End Sub



1. before running Solver, always run freeze
2. after running Solver, run thaw

 

[rml]

We have a pop-up....see remarks below

Here are our lines for module 1
____
Sub listum()
With ActiveWorkbook
If .Names.Count > 0 Then
For i = 1 To .Names.Count
MsgBox (i & " " & .Names(i).Name & " " & Range(.Names(i)).Address)
Next
End If
End With
End Sub
Sub main2()
Dim r As Range
Dim rr As Range
Dim s As String
With ActiveWorkbook
c = .Names.Count
If c > 1 Then
For i = c To 1 Step -1
If .Names(i).Name = "GROUP_1" Then
..Names("GROUP_1").Delete
End If
If .Names(i).Name = "GROUP_2" Then
..Names("GROUP_2").Delete
End If
Next
End If
For Each r In Range("GROUP1")
If IsEmpty(r) Then
If rr Is Nothing Then
Set rr = r
Else
Set rr = Union(rr, r)
End If
End If
Next
If rr Is Nothing Then
Else
s = rr.Address(ReferenceStyle:=xlR1C1)
..Names.Add Name:="GROUP_1", RefersToR1C1:="=DYNANAME!" & s
End If

Set rr = Nothing
For Each r In Range("GROUP1")
If Not IsEmpty(r) Then
If rr Is Nothing Then
Set rr = r
Else
Set rr = Union(rr, r)
End If
End If
Next
If rr Is Nothing Then
Else
s = rr.Address(ReferenceStyle:=xlR1C1)
..Names.Add Name:="GROUP_2", RefersToR1C1:="=DYNANAME!" & s
End If

Call listum

End With
End Sub
Sub freeze()
Application.EnableEvents = False
End Sub

Sub thaw()
Application.EnableEvents = True
End Sub
__________________________

Run time Error 1004
Method Range of Object _ Global failed...

when i click "Debug"
VBE open...
highlighted line is...
For your test, if you have time today....here is the workbook detail
Sheetname : DYNANAME
GROUP1 = A1:E5
GROUP_1 = B1:E5 = ALL BLANKS
GROUP_2 = A1:A5 = ALL FILLED IN WITH A VALUE OF 1.
formulas on F6 = sum(GROUP1)....at this moment is = 5

click Tools> SOLVER :
TARGET CELL = F6 : FOR A VALUE OF 26
CHANGING CELL = GROUP_1
CONSTRAINT>ADD>
GROUP_1 >= 1
hit SOLVE...
then Pop-ups will appear, every 4th pop-up you will find the error window..

I believe it is possible cause you did a lot for this sake....
Thanks for not closing this thread....til then..