konradz
New Member
- Joined
- May 29, 2012
- Messages
- 8
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:
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:
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.
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:
Code:
=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)
- User clicks on the button and form to select the range appears
- The selected range is passed and replaces the tbl range in the formula above
- $B$8:B8 - changes to the cell above the selected cell (if top cell is active error message appears)
- 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.