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.

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:P13)>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
(E-Mail Removed)



"BABs" <(E-Mail Removed)> wrote in message
news:8783FEC4-C441-4D99-9870-(E-Mail Removed)...
>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 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:P13)>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
> (E-Mail Removed)
>
>
>
> "BABs" <(E-Mail Removed)> wrote in message
> news:8783FEC4-C441-4D99-9870-(E-Mail Removed)...
> >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.
>
>

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

--

Regards,
Nigel
(E-Mail Removed)



"BABs" <(E-Mail Removed)> wrote in message
news:9603F087-6A25-4238-828C-(E-Mail Removed)...
> 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:P13)>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
>> (E-Mail Removed)
>>
>>
>>
>> "BABs" <(E-Mail Removed)> wrote in message
>> news:8783FEC4-C441-4D99-9870-(E-Mail Removed)...
>> >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,
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
> (E-Mail Removed)
>
>
>
> "BABs" <(E-Mail Removed)> wrote in message
> news:9603F087-6A25-4238-828C-(E-Mail Removed)...
> > 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:P13)>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
> >> (E-Mail Removed)
> >>
> >>
> >>
> >> "BABs" <(E-Mail Removed)> wrote in message
> >> news:8783FEC4-C441-4D99-9870-(E-Mail Removed)...
> >> >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.
> >>
> >>
>
>

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:P13)>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:P13)>0,K9>=L2),1,0)


--

Regards,
Nigel
(E-Mail Removed)



"BABs" <(E-Mail Removed)> wrote in message
news:CC4BBBC3-F863-44EA-8EE4-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>>
>>
>>
>> "BABs" <(E-Mail Removed)> wrote in message
>> news:9603F087-6A25-4238-828C-(E-Mail Removed)...
>> > 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:P13)>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
>> >> (E-Mail Removed)
>> >>
>> >>
>> >>
>> >> "BABs" <(E-Mail Removed)> wrote in message
>> >> news:8783FEC4-C441-4D99-9870-(E-Mail Removed)...
>> >> >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,
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:P13)>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:P13)>0,K9>=L2),1,0)
>
>
> --
>
> Regards,
> Nigel
> (E-Mail Removed)
>
>
>
> "BABs" <(E-Mail Removed)> wrote in message
> news:CC4BBBC3-F863-44EA-8EE4-(E-Mail Removed)...
> > 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
> >> (E-Mail Removed)
> >>
> >>
> >>
> >> "BABs" <(E-Mail Removed)> wrote in message
> >> news:9603F087-6A25-4238-828C-(E-Mail Removed)...
> >> > 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:P13)>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
> >> >> (E-Mail Removed)
> >> >>
> >> >>
> >> >>
> >> >> "BABs" <(E-Mail Removed)> wrote in message
> >> >> news:8783FEC4-C441-4D99-9870-(E-Mail Removed)...
> >> >> >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.
> >> >>
> >> >>
> >>
> >>
>
>