Enter array formula results

[sogc]

I am using an array formula to find a value, but I want to enter the resulting value of the array and not the array formula itself.

Right now I am using ActiveCell.FormulaArray = "array formula...". But what I really want is to enter the value of that array formula.

 

[SkipVought]

Hi,

Rather than speaking in hypotheticals, why not just post the code that you are actually using, and explain what is happening.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[sogc]

Here is the code I am currently using to enter an array formula into the activecell in a worksheet.

ActiveCell.FormulaArray = "=INDEX(GamesList,SMALL(IF(GamesList=SelectedSessionIndex,ROW(GamesList)-ROW(GamesListFirstPosition),ROW(GamesListLastPosition)),5),9)"

What I would like to do is evaluate this formula and enter the results of the array formula in the active cell.

 

[SkipVought]

With ActiveCell.FormulaArray = "=INDEX(GamesList,SMALL(IF(GamesList=SelectedSessionIndex,ROW(GamesList)-ROW(GamesListFirstPosition),ROW(GamesListLastPosition)),5),9)"
   .Copy
   .PasteSpecial xlpastevalues
End With

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[SkipVought]

...check that precious statement...

With ActiveCell
   .FormulaArray = "=INDEX(GamesList,SMALL(IF(GamesList=SelectedSessionIndex,ROW(GamesList)-ROW(GamesListFirstPosition),ROW(GamesListLastPosition)),5),9)"
   .Copy
   .PasteSpecial xlpastevalues
End With

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[sogc]

Thanks Skip.