Macro to insert array formula

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 para
meter 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)
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.
 

Forum statistics

Threads
1,077,784
Messages
5,336,328
Members
399,076
Latest member
vullistax

Some videos you may like

This Week's Hot Topics

Top