Code to find the max value for a cell while looking at other cells

[BABs]

I have a sheet that does numerous calculations that all hinge off of one cell
(L9). Currently, after I enter in the test data, I manually change the
number in L9 until I get the maximum value in the production field (R13).
While changing the value in L9, I have to pay attention to the values in
cells M10 and N10 to make sure they aren't > 100, and that cells L13 thru P13
aren't >100 or <0.
How can I code a button or macro to run a range of values for L9 to find the
max value for R13 where M10 and N10 are <100 and cells L13 thru P13 are <100
and >0?
Thx in adv.

 

[Nigel]

Sounds like a linear programming model ?

However keeping it simple, and I am making a few assumptions here.

You need a start and increment values to be entered, lets say cells L6 =
start value and L7 = incremental value

Add a formula to set up a stooping value from the control range M10, N10 and
L13 to P13 do not exceed 100, I will assume this is added in L8

The formula needs to show 1 if all the values are <100 and >0 otherwise 0

in Cell L8 put:

=IF(AND(MAX(M10:N10,L1313)<100,MIN(M10:N10,L1313)>0),1,0)

Code you require to run

Sub TestModel()
With Sheets("Sheet1")
' set start up value in L9
.Range("L9") = .Range("L6")

' increment value until stopping condition is meet
Do While .Range("L8") = 1
.Range("L9") = .Range("L9") + .Range("L7")
Loop
End With
End Sub

As I said this is very basic and you may need to put in forced stopping
values etc.

 

[BABs]

Nigel thank you. I inserted a command button and put your code to it. When
I click the button, the code window opens and that's it.
On click, the code should look at H2,
if the value is 2.2 then
L9 starts at 30
Else
L9 starts at 20
End if
do while T9 = 1 (I put the if formula into T9)
L9 = L9 + 1
Loop

I am missing something and can't seem to get it to work.
Help please!

 

[Nigel]

You must have modified the code, suggest you post the code with information
about where the control data is stored.

--

Regards,
Nigel
(e-mail address removed)

 

[BABs]

Nigel,
I found the problem, so simple I totally overlooked it. I wasn't working on
sheet1, it was actually sheet2 "template". I changed that in the code and it
worked fine.
Now I'm trying to figure out how to adjust the formula that you gave me for
L8. I have to also have it check and make sure that K9 is greater than or
equal to M2. How can I add this to the IF/AND formula for L8?
TIA

 

[Nigel]

In general the formula repeated below for L8 test the Max and Min values in
a range(s) of cells.

=IF(AND(MAX(M10:N10,L1313)<100,MIN(M10:N10,L1313)>0),1,0)

These are M10:N10 and L1313, each range is separated by a comma. The
second part is that these two conditions are ANDed together to control the
logic. If both the MIN or MAX conditions are meet their value will be a
logic TRUE or numerically a 1. To add another condition include it inside
the AND condition therefore the additional test for K9 >= L2, should also
be TRUE

see result below....

=IF(AND(MAX(M10:N10,L1313)<100,MIN(M10:N10,L1313)>0,K9>=L2),1,0)


--

Regards,
Nigel
(e-mail address removed)

 

[BABs]

Nigel,
Thanks so much for your help. I ended up using the statement below and it
seems to be working fine.

=IF(AND(MAX(M10:N10,L1313)<=100,MIN(M10:N10,L1313,L1414,L1515)>=0),IF(K9>=T8,1,0),0)

Is there some way that I can have the workbook ask me for a value to insert
in a field when I open it (similar to the ASK fields in Word)?