How to freeze dynamic NAME with SOLVER

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

From my previous post yesterday "DYNAMIC NAME"(worksheet function forum), 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.
 
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
 
thanks....i'll test it now....

Gary''s Student said:
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
Click to expand...
 
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..
 
I have not been able to get the same error pop-up.


REMEMBER:

Before you click Tools > Solver, you MUST disable main2 by running freeze.
The sequence will look like:

1. run thaw
2. set-up GROUP1 manually
3. run freeze
4. run Solver

You can repeat this if you need more manual set-ups.
 
You are perfectly right, I forgot to click Tools>Macro>Run
you got a habit of helping....a thousand thanks mr. wizard...
 
Back
Top