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

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.

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

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,L13:P13)<100,MIN(M10:N10,L13:P 13)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.



--

Regards,
Nigel




"BABs" wrote in message
...
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.

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

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" wrote:

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,L13:P13)<100,MIN(M10:N10,L13:P 13)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.



--

Regards,
Nigel




"BABs" wrote in message
...
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.

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

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

--

Regards,
Nigel




"BABs" wrote in message
...
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" wrote:

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,L13:P13)<100,MIN(M10:N10,L13:P 13)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.



--

Regards,
Nigel




"BABs" wrote in message
...
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.

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

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" wrote:

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

--

Regards,
Nigel




"BABs" wrote in message
...
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" wrote:

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,L13:P13)<100,MIN(M10:N10,L13:P 13)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.



--

Regards,
Nigel




"BABs" wrote in message
...
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.

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

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,L13:P13)<100,MIN(M10:N10,L13:P 13)0),1,0)

These are M10:N10 and L13:P13, 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,L13:P13)<100,MIN(M10:N10,L13:P 13)0,K9=L2),1,0)


--

Regards,
Nigel




"BABs" wrote in message
...
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" wrote:

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

--

Regards,
Nigel




"BABs" wrote in message
...
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" wrote:

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,L13:P13)<100,MIN(M10:N10,L13:P 13)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.



--

Regards,
Nigel




"BABs" wrote in message
...
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.

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

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,L13:P13)<=100,MIN(M10:N10,L13: P13,L14:P14,L15:P15)=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)?






"Nigel" wrote:

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,L13:P13)<100,MIN(M10:N10,L13:P 13)0),1,0)

These are M10:N10 and L13:P13, 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,L13:P13)<100,MIN(M10:N10,L13:P 13)0,K9=L2),1,0)


--

Regards,
Nigel




"BABs" wrote in message
...
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" wrote:

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

--

Regards,
Nigel




"BABs" wrote in message
...
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" wrote:

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,L13:P13)<100,MIN(M10:N10,L13:P 13)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.



--

Regards,
Nigel




"BABs" wrote in message
...
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.