Finding Max and min value while varying 2 constraints, for loop

Hello,

My current code varies the cell Worksheets("Module 1 State 1").Range("B10") with the values ented in A2: A38 and returns the values from Worksheets("Module 6").Range("F12") and Worksheets("Module 6").Range("F8") for each iteration. For this part Worksheets("Module 1 State 1").Range("B8").Value = 2.4 is a constant.

Now I have to continue to vary the cell Worksheets("Module 1 State 1").Range("B10") with the values ented in A2: A38 at the same time vary each Worksheets("Module 1 State 1").Range("B8") with the values entered in B2: B101 . I need to know what value in range B2: B101 will give me max number in for Worksheets("Module 6").Range("F12") and which one will give me min Worksheets("Module 6").Range("F8") for each iteration value in range A2: A38.

Sorry my explanation is a little unclear, so here is a picture.
project.JPG

So example for Altitude= 2000 what Mach 1 wil maximize Overall Efficiency and Mach 1 will minimize TSFC. And repeat for the altitude value...






The following is the code I have the first

Please Login or Register  to view this content.

Stargirl
Instead of a picture, add a before and after workbook so people don't have to make one up to try code.

project1.JPG

This is the original. The constant for mach 1 is in Worksheets("Module 1 State 1").Range("B8").Value = 2.4.

Please Login or Register  to view this content.

The new scenerio is the one attached in original post now. Worksheets("Module 1 State 1").Range("B8").Value has to vary from the values in B2: B101. and in the new added columns I want the code to tell me for each Altitude what is the mach number that will give me max and min efficiency and TSFC respectively.

I would greatly appreciate it if you could help

Sorry I am trying to add excel file...help.xlsm

Sheet "Part D"

In Sheet "Module 1 State 1", where can we find mach number for max eff, mach number for mim eff and TSFC?
In your added workbook, the values (Text?) in all the module sheets are not readable.

Better yet, I think, just fill in the blanks here.
From Sheets("Part D"), copy the value of cell A2 into sheet "Module 1 State 1" cell B8. Right or wrong?
If wrong, which worksheet and which cell?

From Sheets("Part D"), copy the value of cell B2 into sheet "?????????" cell ??.
Which worksheet and which cell?
The formulae will do their work.
Copy the value from Sheet "?????????", cell ?? Into Sheet "Part D" cell C2
Copy the value from Sheet "?????????", cell ?? Into Sheet "Part D" cell D2
Copy the value from Sheet "?????????", cell ?? Into Sheet "Part D" cell E2
Copy the value from Sheet "?????????", cell ?? Into Sheet "Part D" cell F2

Now start over but instead of the 2nd row, we'll work with the 3rd row.

That's what I am trying to find.

So example, for altitude z= 2,000 m the code needs to run all the values for Mach 1 B2:B101.
Z= 2,000 M1= 0.1 OE_1 TSFC_1
M2= 0.15 OE_2 TSFC_2
M3= 0.2 OE_3 TSFC_3

... .... ....
Then I need to know what will be the max Overall efficiency from that output array and the min TSFC from the output array.
I need those max and min values to be displayed on the desired cells and also display the mach numbers that make those values max or min.

Then repeat for altitude 2500, 3000, ... etc.

hope that helps ...

No that does not help at all. You're not answering simple questions.
If you don't want to help yourself, I can't help you.
Read my last post again, let it sink in what I say and ask for.
Read the line about readability!!!!!

Sorry,
No its backward copy value

Sheet "Part D" cell A2 to Sheet "Module 1 State 1", cell B10
Sheet "Part D" cell B2 to Sheet "Module 1 State 1", cell B8

Sheet "Part D" cell C2 (thats what we are trying to solve)
Copy Value from Sheet "Module 6", cell F12 to Sheet "Part D" cell D2
Sheet "Part D" cell E2 (thats what we are trying to solve)
Copy Value from Sheet "Module 6", cell F8 to Sheet "Part D" cell F2

Oh good. That gives us something to work with stargirl.
Thanks and I'll see what we can come up with.
Thanks again.

At 5500 Altitude and 0.1 Mach 1 it starts throwing errors.
Should it be?

I think I misunderstood what you were after before. My Apologies.

Compare some of the resulting values with values you know are right before we continue on the wrong path please.

Please Login or Register  to view this content.

If you look at Sheet1, which I added, and run the code from "For Sheet 1", are these results right?