Hello,

In F1 I enter a target number value.
In D1 I also enter any number between -9 and +9.
I have an existing macro which computes my spreadsheet and
produces a number result in L21.
My macro compares the numbers in F1 and L21 and proceeds
to eliminates selected records one at a time until the
result in L21 is the closest to, BUT not less than F1.
So far everything works as it should.

I also have another cell C2 into which I enter data (it is
of no consequence here though) and combined with the entry
in D1 allows me to explore a vast range of possibilities.

However, at the moment I enter the numbers -9 to +9 in D1,
one at a time, run my macro after each entry, and observe
the result, on which I make my decisions. My existing
macro, based on whatever number is in D1, always
completes the cycle at the point where the result in L21
is as close as possible to F1 but never below it. It does
this by progressively eliminating certain records to
achieve it.

I wish to create a new macro that will cycle through the
number range, each time running my existing macro
until the number produced by the existing macro in L21 is
as close as possible but not less than F1. The number that
does this should be left in F1 when the macro stops
running, as this number is also part of the whole scenario
and triggers other actions on the spreadsheet.

It's a long way of saying how do I get a macro to
automatically insert and cycle the numbers in F1 and run
my existing macro until the criteria is met.

I have had a go at it and my code is below. I can see the
numbers being introduced into cell F1, but the macro
always stops on 1 (which is the first number in my array)
and does not seem to change anything else.

I think I've done something dumb and would respectfully
ask if anyone can help me fix the code.

Thankyou,

Sub Family()
ActiveSheet.Unprotect
Application.ScreenUpdating = False

Dim myBest As Variant
Dim maxName As String
Dim MaxVal As Double
Dim i As Long
Dim First As Boolean
arr = Array(1, 2, 3, 4, , 5, 6, 7, 8, 9, -1, -2, -3, -
4, -5, -6, -7, -8, -9)


myBest = Array("Go_Prior1850")

First = True
For i = LBound(arr) To UBound(arr)
Range("D1") = arr(i)

If First Then
Application.Run "ALL_FAMILY.xls!" & myBest(i)
If i = LBound(arr) Then
MaxVal = Range("L21").Value
First = False
maxName = arr(i)
outer = Array(i)
ElseIf Range("L21").Value = MaxVal Then
MaxVal = Range("L21").Value
maxName = arr(i)
outer = Array(i)
End If
End If
Next

Range("D1").Value = arr(outer(0))

Application.Run maxName

Application.ScreenUpdating = True
ActiveSheet.Protect DrawingObjects:=True,
Contents:=True, Scenarios _
:=True
End Sub

Hi Tom,
I forgot to mention in my spec below that the ALL_FAMILY
macro is the one that eliminates the records. I just need
a macro as below that runs it repeatedly after trying each
array number in the cell.
Can u help,
Regards,
Rick

-----Original Message-----
Hello Tom,

Still trying to get my head around this. What you say is
right. But I don't want to change my existing macro as it
is running exactly as I require and it took me so long to
get it debugged, I couldn't go thru that again. I've also
goy so many things dependant on other macro's and
formulas, I think I will lose track of where I'm at.

So how could I modify my code to run 18 (I can easily
decrease this range in the array if needed), times and
then stop on the number in D1 that gives me the closest
result to F1.

i.e.
1. Start new macro, which enters -1 in D1 and continues.
This macro then runs my existing macro ALL_FAMILY and
captures the number produced in L21 and stores it.

2. The macro then goes back to D1 and enters -2 and away
it goes again and again stores the number produced in
L21.

3. It continues like this until all 18 variables have
been
tested.

4. After all cycles have completed, it then compares the
18 numbers and to F1 and selects the closest number to
that in F1. Then, having found that number it leaves or
enters as the case may be, that number in D1, then runs
the macro again using this candidate number. This will be
the final time the macro runs, which then executes my
existing macro again, in turn computing my spreadsheet
and
giving me the result needed.

5. If, when the macro is running through the 18 cycles,
it
does not produce any number in L21 (a stored number),
which is greater than F1, i.e they are all less, a
message
could display saying "No valid candidates found. Change
parameter in C2". The macro would stop executing.

Tom, this probably is a most cumbersome way of doing it
and even though the macro cycles many times, it is very
quick on my machine so presents no problem with speed.

This is what I've tried to do with my example code, but
I'm getting confused. Could you help.

Thanks
Rick


-----Original Message-----
I ran this version of your code:
Sub Family()
' ActiveSheet.Unprotect
' Application.ScreenUpdating = False

Dim myBest As Variant
Dim maxName As String
Dim MaxVal As Double
Dim i As Long
Dim First As Boolean
arr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, _
-1, -2, -3, -4, -5, -6, -7, -8, -9)


myBest = Array("Go_Prior1850")

First = True
For i = LBound(arr) To UBound(arr)
Range("D1") = arr(i)
Debug.Print i, arr(i)
If First Then
' Application.Run "ALL_FAMILY.xls!" & myBest(i)
If i = LBound(arr) Then
MaxVal = Range("L21").Value
First = False
maxName = arr(i)
outer = Array(i)
ElseIf Range("L21").Value = MaxVal Then
MaxVal = Range("L21").Value
maxName = arr(i)
outer = Array(i)
End If
End If
Next

Range("D1").Value = arr(outer(0))

' Application.Run maxName

' Application.ScreenUpdating = True
' ActiveSheet.Protect DrawingObjects:=True,
Contents:=True, Scenarios _
:=True
End Sub


and it produced:
0 1
1 2
2 3
3 4
4 5
5 6
6 7
7 8
8 9
9 -1
10 -2
11 -3
12 -4
13 -5
14 -6
15 -7
16 -8
17 -9


I suspect, however that when you change the value in D1,
it affects the
value in L21. Thus, your else statement is probably
never run because it
only runs when L21 equals the value of maxvalue (which
is
L21 before the
value in D1 is changed). So, if the value of L21 is not
equal for both D1 =
1 and D1 = 2, the macro would not execute the elseif
portion. Your macro
doesn't look at F1, so I am not sure how that figures
into the game.

Besides running
ALL_FAMILY.xls!Go_Prior1850
on the first loop, you never do anything that would
elimate records.



Regards,
Tom Ogilvy


"Rick" wrote in message
...
Hello,

In F1 I enter a target number value.
In D1 I also enter any number between -9 and +9.
I have an existing macro which computes my spreadsheet
and
produces a number result in L21.
My macro compares the numbers in F1 and L21 and
proceeds
to eliminates selected records one at a time until the
result in L21 is the closest to, BUT not less than F1.
So far everything works as it should.

I also have another cell C2 into which I enter data
(it
is
of no consequence here though) and combined with the
entry
in D1 allows me to explore a vast range of
possibilities.

However, at the moment I enter the numbers -9 to +9 in
D1,
one at a time, run my macro after each entry, and
observe
the result, on which I make my decisions. My existing
macro, based on whatever number is in D1, always
completes the cycle at the point where the result in
L21
is as close as possible to F1 but never below it. It
does
this by progressively eliminating certain records to
achieve it.

I wish to create a new macro that will cycle through
the
number range, each time running my existing macro
until the number produced by the existing macro in L21
is
as close as possible but not less than F1. The number
that
does this should be left in F1 when the macro stops
running, as this number is also part of the whole
scenario
and triggers other actions on the spreadsheet.

It's a long way of saying how do I get a macro to
automatically insert and cycle the numbers in F1 and
run
my existing macro until the criteria is met.

I have had a go at it and my code is below. I can see
the
numbers being introduced into cell F1, but the macro
always stops on 1 (which is the first number in my
array)
and does not seem to change anything else.

I think I've done something dumb and would respectfully
ask if anyone can help me fix the code.

Thankyou,

Sub Family()
ActiveSheet.Unprotect
Application.ScreenUpdating = False

Dim myBest As Variant
Dim maxName As String
Dim MaxVal As Double
Dim i As Long
Dim First As Boolean
arr = Array(1, 2, 3, 4, , 5, 6, 7, 8, 9, -1, -2, -
3, -
4, -5, -6, -7, -8, -9)


myBest = Array("Go_Prior1850")

First = True
For i = LBound(arr) To UBound(arr)
Range("D1") = arr(i)

If First Then
Application.Run "ALL_FAMILY.xls!" & myBest(i)
If i = LBound(arr) Then
MaxVal = Range("L21").Value
First = False
maxName = arr(i)
outer = Array(i)
ElseIf Range("L21").Value = MaxVal Then
MaxVal = Range("L21").Value
maxName = arr(i)
outer = Array(i)
End If
End If
Next

Range("D1").Value = arr(outer(0))

Application.Run maxName

Application.ScreenUpdating = True
ActiveSheet.Protect DrawingObjects:=True,
Contents:=True, Scenarios _
:=True
End Sub



.

.