R
Rick
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
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