Combine Array formula with VBA code

[kabammi]

Hi all,

Thanks for reading this, and being patient with me. I'm new to VBA programming for Excel, and need a little help.

I've written some code to automatically parse a CSV spreadsheet, to set up a bunch of formulas that calculate/evaluate using SUBTOTAL so that they will actively recalculate values based on Autofiltering.

As we all know, there is no MEDIAN subtotal function in Excel, so I'm using the following code as an Array formula (CTRL+SHIFT+ENTER), and it works nicely:

Please Login or Register  to view this content.

FWIW, my data is in Rows 11 onward. I use the ridiculously long terminal row because incoming data is of different row numbers, and this was the simplest way of doing it at the time.
I want to fix this, its a massive array and while it's fast on my PC, its quite slow on others. So when I run the macro that inserts this array formula into an active worksheet, I want it to insert the number of the last row dynamically depending on how many rows there are in the active worksheet.

To Calculate the mean I use this:

Please Login or Register  to view this content.

The " & ActiveSheet.Cells.SpecialCells(xlLastCell).Row & " statement seems to do the trick nicely in VBA code for regular formulas.

So I wanted to use a similar thing to apply the number for the last row to the Array formula, but it does not work..

I recorded a macro when I pasted the above MEDIAN formula, and got this:

Please Login or Register  to view this content.

so I did a very hacky thing, that worked for the regular Subtotal functions (non-array), but it doesnt work with this array function:

Please Login or Register  to view this content.

VBA/Excel tells me "Unable to set the FormulaArray property of the Range class."
So something is quite wrong with how I've set the variable range.

Could anyone please point me in the right direction? Everything else seems to be working nicely, its just this bit that has me stumped.

thanks,
best regards
K.

[StephenR]

You just need to double-up the quotes in VBA:

Please Login or Register  to view this content.

[kabammi]

Its funny, I tried putting in that end (double up) quote manually, and it gave me a compile error.
When I pasted yours in, it just worked.


Thanks Kindly!