Min and Max table values over 10 iteration cycle

[Brian F]

I have a short VBA program that starts with a value of 0 degrees and keeps adding 20 degrees (or whatever I specify) so as to work its way around a circle. Those angles are used to build a table based on various Excel calculations. Let's consider the table to be A1:A10. The initial input values for the table can be any number so the minimum and maximum values of the table can be any number as the table goes through a cycle of calculations.

I need to know what the Min and Max values are in a column of the table as it goes through one complete cycle (0 to 360 degrees).

To simplify; the table changes 10 times in one cycle. I want to know the minimum and maximum values attained in any one column of the table as it goes through the 10 iterations that make up one complete cycle.

I am not looking for the minimum and maximum of the VBA that drives the table (that is always 0 and 360 degrees). I need to know the mins and maxs of the table which is the result of Excel calculation. So, I need a VBA procedure that looks at the table and keeps track of the min and max of each column of values as it goes through one complete cycle and reports that value to me.

I know it's a lot but any help would be greatly appreciate!

 

[AlphaFrog]

<font face=Courier New>    <SPAN style="color:#00007F">Dim</SPAN> FinalMin <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    FinalMin = ""<br>    <br>    <SPAN style="color:#00007F">For</SPAN> deg = 0 <SPAN style="color:#00007F">To</SPAN> 360 <SPAN style="color:#00007F">Step</SPAN> 20<br>            <SPAN style="color:#007F00">'Calculate column A here</SPAN><br>        <br>        CurrentMin = WorksheetFunction.Min(Range("A1:A10"))<br>        <SPAN style="color:#00007F">If</SPAN> CurrentMin < FinalMin <SPAN style="color:#00007F">Or</SPAN> FinalMin = "" <SPAN style="color:#00007F">Then</SPAN> FinalMin = CurrentMin<br>    <SPAN style="color:#00007F">Next</SPAN> deg<br>    <br>    MsgBox "The lowest value in A1:A10 was " & FinalMin</FONT>

 

[Brian F]

Thank you. That looks good. I'm at work and can't stop to try it but when I do I'll let you know how it works.

Thanks again

 

[Brian F]

AlphaFrog,

This worked perfectly. Of course my Workbook is somewhat more complicated than I described but using your information I was able to determine the min and max values for the horizontal and vertical axis on 6 different charts and then went on to automatically apply the values to the charts. So, with one click I sorted through a mountain or changing data and automatically set up 6 charts.

Thank you so much.