How to freeze dynamic NAME with SOLVER

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rml - 10 Nov 2006 19:26 GMT

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.

Reply to this Message

Gary''s Student - 11 Nov 2006 11:01 GMT

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

Signature

Gary's Student

> From my previous post yesterday "DYNAMIC NAME"(worksheet function forum), I
> had just been fed by Gary's
[quoted text clipped - 6 lines]
> I hope Gary's Student can read this thread...to further continue the
> multitask involve on the spreadsheet I am doing.

Reply to this Message

rml - 11 Nov 2006 13:04 GMT

thanks....i'll test it now....

> I finally got it.
>
[quoted text clipped - 25 lines]
> > I hope Gary's Student can read this thread...to further continue the
> > multitask involve on the spreadsheet I am doing.

Reply to this Message

rml - 11 Nov 2006 14:20 GMT

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...

>>>>MsgBox (i & " " & .Names(i).Name & " " & Range(.Names(i)).Address)

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 finally got it.
>
[quoted text clipped - 25 lines]
> > I hope Gary's Student can read this thread...to further continue the
> > multitask involve on the spreadsheet I am doing.

Reply to this Message

Gary''s Student - 13 Nov 2006 17:53 GMT

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.

Signature

Gary''s Student

> We have a pop-up....see remarks below
>
[quoted text clipped - 124 lines]
> > > I hope Gary's Student can read this thread...to further continue the
> > > multitask involve on the spreadsheet I am doing.

Reply to this Message

rml - 13 Nov 2006 20:12 GMT

You are perfectly right, I forgot to click Tools>Macro>Run
you got a habit of helping....a thousand thanks mr. wizard...

> I have not been able to get the same error pop-up.
>
[quoted text clipped - 138 lines]
> > > > I hope Gary's Student can read this thread...to further continue the
> > > > multitask involve on the spreadsheet I am doing.

Reply to this Message