PhillipY

Find the region containing the maxium values in What-If-Analysis matrix (C3:X24).

Note that the function are {Array Functions} enterd using "ctrl+shift+enter".

row of first max ={(MIN(IF((C3:X24)=MAX(C3:X24),ROW(C3:X24))))}

column of first max ={(MIN(IF((C3:X24)=MAX(C3:X24),COLUMN(C3:X24))))}

row of last max ={(MAX(IF((C3:X24)=MAX(C3:X24),ROW(C3:X24))))}

column of last max ={(MAX(IF((C3:X24)=MAX(C3:X24),COLUMN(C3:X24))))}

I need to transfer this from Excel to VBA, First how do you enter an Excell array function in VBA it must be more complicated than using "Applicaton.WorksheetFunction."

Second Make C3 and X24 in to variables.

I am building a solver for a system that use the "IF" function (a lot of them),
Excel Solvers wont allow the "IF" function, solvers work with normal math functions not logic functions, (the function has 2 variables to be optimised) the What-If-Analysis almost works.

My approach is since it will take to long to try every posable combination I use a Coarse What-If-Analysis matrix then the above functions to find the region containing the maxium values, then use a Fine What-If-Analysis matrix to zoom in on the region with more precision.

The reason for moving from excel to VBA is that Excel looses track of which came first the fine or the coarse What-If-Analysis matrix and produces results that are correct every other time that the test is done, it takes a long time to make a summary of the results indexed with a 3rd variable, hours for each sunmmary item and days for the hole thing.

Is ther a language better suited for array functions like this ?