Macro to insert array formula

[konradz]

Hi,

I'm working on a simple macro that would insert a simple sorting formula depending on the user selection. I took few lines of code to call a form enabling the range selection. I would like to pass the selection value from the form as the tbl parameter in this formula:

=INDEX(tbl, MIN(IF(SMALL(COUNTIF(tbl, "<"&tbl),  ROWS($B$8:B8))=COUNTIF(tbl, "<"&tbl), ROW(tbl)-MIN(ROW(tbl))+1)),  MATCH(SMALL(COUNTIF(tbl, "<"&tbl), ROWS($B$8:B8)), COUNTIF(tbl,  "<"&INDEX(tbl, MIN(IF(SMALL(COUNTIF(tbl, "<"&tbl),  ROWS($B$8:B8))=COUNTIF(tbl, "<"&tbl), ROW(tbl)-MIN(ROW(tbl))+1)),  , 1)), 0), 1)

In addition, I would like to replace the $B$8:B8, with the above cell from the currently selected cell. So in a word four things have to happen:

1. User clicks on the button and form to select the range appears
2. The selected range is passed and replaces the tbl range in the formula above
3. $B$8:B8 - changes to the cell above the selected cell (if top cell is active error message appears)
4. Index array formula is inserted to the currently active cell

I often use this sorting formula and it would be easier to develop a macro inserting the formula automatically instead of inserting it manually all the time. I will greatly appreciate any help.